I’m presenting right now at the Professional Association for SQL Server Summit in Seattle, and I’m just about to show my attendees the latest incarnation of my Blitz script.
In the past, my manual Blitz script has helped DBAs all over the world discover ugly problems in SQL Servers. However, one thing has been bothering me a lot for the last year or so. One of the lines in the script said, “Change this email address to your own, then execute this line and make sure you can receive emails from Database Mail.” You can probably guess what happened – a lot of people didn’t bother changing the email address. I used my own email in the script because I wanted to know when people ran it that way, and I got at least a dozen emails a week. I talked to some of the users, and they confessed that they didn’t even bother to read the script – they just clicked F5.
At first that bugged me. The Blitz script really has two purposes: to enlighten DBAs about their SQL Server’s risks, and to start teaching the basics of DMVs. These users weren’t learning anything – they just wanted to run the script and make the magic happen. So I figured – why not give it to ’em?
sp_Blitz®: One-Minute Server Takeovers
Say hello to sp_Blitz® – a simple stored procedure that runs a bunch of health checks and exports the results in a prioritized list. Here’s what the output looks like:
The URL column includes a link for each problem we found in the SQL Server. Copy/paste that link into a web browser and you’ll be able to learn more about the particular issue you’re facing. I also include a quick snippet about the general source of the data, like which DMVs I’m querying to catch the issue.
The stored procedure can take a minute or two to run on larger servers, and this is very much a version 1.0. If you find things you’d like to improve, please feel free to let me know – especially if you include sample code to improve it, heh. I expect sp_Blitz® to undergo some rapid improvements over the coming weeks as people holler about bugs, which leads me to the next fun idea I’m playing with in the session.
28 Comments. Leave new
This is excellent Brent,loved it
Excellent idea.
However, although in the SP header you say that SQL2000 isn’t supported, SQL2005 fails as well.
You’re utalising these DMVs that don’t exist in 2005:
sys.dm_server_audit_status
sys.resource_governor_configuration
sys.dm_db_persisted_sku_features
sys.dm_db_mirroring_auto_page_repair
and the is_encrypted column in sys.databases.
Dave – ah, great catch! I’ll tweak the script today to make that work too. Thanks for the precise feedback!
Perform global replace everywhere you have @@VERSION either LIKE of NOT LIKE ‘Microsoft SQL Server 2000’ or ‘Microsoft SQL Server 2005’ and add a % sign, i.e. ‘Microsoft SQL Server 2000%’ or ‘Microsoft SQL Server 2005%’
There appear to be other issues as well. INSERT INTO #BlitzResults starting on line 772 SELECTs from sys.dm_db_mirroring_auto_page_repair, which returns an Invalid object name error in SQL 2005 sp4. Still looking for other possible problems
add sys.dm_os_sys_memory to that list for 2005. For sys.dm_db_mirroring_auto_page_repair I just moved the “END” to the end of that section so the 2000,2005 check would pass over the whole block:
FROM sys.dm_db_mirroring_auto_page_repair
WHERE modification_time >= DATEADD(dd, -30, GETDATE()) ;
END ;
I was in your session today, very informative. Thanks for sharing your work. I am going to give it a try tonight.
Hi Brent,
This was a nice all in one script! 🙂
But, regarding “lazy” DBA’s, what about including the commands in the recommendations also? Eg for PAGE CHECKSUM, if you alter #BlitzResults to include SQLCmd VARCHAR(200), and then in the @StringToExecute add this after “AS Details,”:
(”ALTER DATABASE [” + [name] + ”] SET PAGE_VERIFY CHECKSUM;”) as SQLCmd
😉
I modified my copy that way 😀
-Magne
@mfretheim
Magne – thanks, glad you liked it! That’s an interesting idea – I like it. I use that same technique on my index maintenance scripts. I’ll add that in over time. Thanks!
Speaking about PAGE_VERIFY: “Database [tempdb] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.”
It is really cool, and it took about 58 seconds to get the script. I will dig into the script a little bit 🙂
Nice work, Brent.
Great Stuff as always, Brent ! 🙂
Hey Brent, where do you prefer to receive feedback and suggestions for sp_Blitz?
Nick – oo, that’s a great question. For starters I’ll do it here, but I should probably set up something like UserVoice, eh? Is there a format you’ve found that you like the most? This is my first time putting one of those together.
There are several options, sorted from most preferred to least:
1. Post the code to a public repository like github/gist that lets you accept comments and review/accept suggested edits.
2. Add a UserVoice module to your main sp_Blitz page.
3. Create an inbox specifically for sp_Blitz feeback and include that email address in your code and on the main sp_Blitz page.
4. Start a thread on a forum or DBA.SE and solicit feedback.
Hi Brett
Great script…although i think you need to update your LIKE statements to include % at the end of 2000 and 2005..I think that’s why Dave Dustin had his issues
I had to add the percents to make the NOT LIKEs work on 2005:
from
IF @@VERSION NOT LIKE ‘Microsoft SQL Server 2000’
to
IF @@VERSION NOT LIKE ‘%Microsoft SQL Server 2000%’
Thanks for the feedback, everybody! I’ve updated a new version that fixes the 2005 % bugs, adds a check for non-trusted check constraints, and added Ali Razeghi’s contribution – a check for databases owned by someone other than SA.
I’m heading back to the PASS Summit sessions, but I’ll set up a UserVoice module to let people request & vote on contributions too. Great idea.
Brett,
Was seeing this error running the script on 2008 R2.
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 6 in SELECT statement.
When running
SELECT 2 AS CheckID ,
1 AS Priority ,
‘Backup’ AS FindingsGroup ,
‘Full Recovery Mode w/o Log Backups’ AS Finding ,
‘https://www.brentozar.com/blitz/full-recovery-mode-without-log-backups/’ AS URL ,
( ‘Database ‘ + d.Name + ‘ is in ‘ + d.recovery_model_desc
+ ‘ recovery mode but has not had a log backup in the last week.’ ) AS Details
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
AND b.type = ‘L’
AND b.backup_finish_date <= DATEADD(dd,
-7, GETDATE())
Doug – that’s interesting. It sounds like you’ve got different collations for the master and msdb databases. You can check it by doing a SELECT * FROM sys.databases and compare the collations there. I’m not sure if that’s a supported configuration, but that brings up a funny point – I should check for that! I’ll add that to the list of things to write.
Hmm – even more interesting – all of my DBs DO have the same collation –
SELECT Name, [collation_name]
FROM sys.databases
WHERE [collation_name] != ‘SQL_Latin1_General_CP1_CI_AS’
— No records returned
Best,
Doug
Interesting – If I run this script on my 2008 R2 box against a SQL 2005 server – it believes the 2005 box is 2008 R2 and thus throws all kinds of incorrect observations.
Just thought you might like to know,
Doug
Doug – can you explain a little more about what you mean? I’m not sure I understand what you’re saying. You can email me at brento@brentozar.com if that’s easier.
When the script finds Foreign Key Not Trusted it gives this URL:
https://www.brentozar.com/blitz/foreign-key-trusted/?VersionNumber=1 which appears to be broken.
Ran out of time fixing my version of the script, but “Backups Not Performed Recently” will run into issues on master.sys.databases instead of dbo.sysdatabases when dealing with SQL2000.
Enough explanation in the comments to patch together a pretty quick fix.
Excellent script as usual Brent, simple and useful
I have some other “diagnostic” query that sometimes are too detailed for a quick look
I look forward to future sp_Blitz v99 🙂
A few things for now
1. you should disclose that sp_Blitz code online contains the sp_Blitz_update at the bottom as well (vs. a complete separate download)
2. I google’d this and still unsure whether it’s REQUIRED by Microsoft..I almost began to remove it from sysadmin everywhere 🙂 but Microsoft KB says we shouldn’t (at least for SQL 2005)
Login [NT AUTHORITY\SYSTEM] is a sysadmin – meaning they can do absolutely anything in SQL Server, including dropping databases or hiding their tracks.
Jerry – Thanks for your feedback!
Brent,
I am missing some things inside your script. It doesn’t check if the guest account is enabled or not. This could be a risk when you assign grants to the public role. So I would also check if the guest account is enabled and the public role has grants.
I like the script as it is very much and have found quit a bit of things in my environment with it.
Thanks,
Jacob
[…] 60 Minute Blitz Script (Brent Ozar) – check it out. It will be worth your time. If you looked at the other lists you will also see that this script is there and highly recommended. […]