Blog

Our free server troubleshooting tool, sp_Blitz™, just learned some new tricks. If you call it like this:

EXEC dbo.sp_Blitz
@OutputDatabaseName = ‘DBAtools’,
@OutputSchemaName = ‘dbo’,
@OutputTableName = ‘BlitzResults’

It will create the table DBAtools.dbo.BlitzResults (if it doesn’t already exist) and insert the results. The table includes columns for the server name and the check date, so you can query this table to see what’s changed since the last time you ran sp_Blitz™. Plus, there’s more:

Added new checks for disabled CPU schedulers (due to licensing or affinity masking), extended stored procedures, disabled remote access to the DAC, databases in unusual states like suspect or emergency, logins with CONTROL SERVER permission.

Easier readability – whenever anyone’s asked how to add checks, I’ve always just said, “Read the code.” Last week, I read the code with fresh eyes and realized it could use some cleanup and comments. I used Red Gate SQL Prompt to clean up the formatting, which is better but could still use some work. The code is nearing 4,000 lines, so I moved the old changes out of the proc and into a new online changelog. I put in some time to explain what the proc is doing, and that should help people reading it from the first time. I’ve also started an sp_Blitz™ Documentation page where I’ll be explaining some of the more advanced uses.

New downloadable sp_Blitz™ app for Windows - over and over, we heard from people that they wanted a quick way to generate a print-friendly copy of the sp_Blitz™ results. Jeremiah worked with a developer and built our first app:

sp_Blitz™ Windows App

The app creates sp_Blitz™ in TempDB on the server of your choice, executes it, shows you the results, and can output the results to PDF – complete with links to the explanations for each result.

We’ve got lots of ideas for the app, but we wanted to get it out there first and let you help drive it. If there’s something you’d like to see added or changed, leave a comment here or contact us. The whole point of this thing is to make your life easier. I wish this tool would have been available back when I was a production DBA struggling to figure out what was going on in my environment, so every little improvement helps. Hopefully this can save you from the stress I had back then. Get it from the download page and enjoy!

There’s a new version in town.  v18 adds new checks looking for:

• Backups stored on the same drive letters as user databases (Thanks @AlinSelicean!)
• Agent jobs that don’t have failure notifications set up (Thanks @Thirster42!)
• Shows free drive space per drive letter if you pass @CheckServerInfo = 1 in. We don’t consider this a problem – we just show additional info if you use this parameter.
• Added the elevated database permissions check back in (whoops! dropped it accidentally)

Plus bug fixes and improvements including:

• Lots and lots of typos
• Ignore max file sizes for filestream data files
• Switched a lot of @@SERVERNAME usage out with SERVERPROPERTY(‘ServerName’) because in some SQL Server cloud implementations, those don’t match, and it’s okay.
• Changed database name variables to be NVARCHAR(128) to handle those monster SharePoint databases
• Improved the non-aligned-indexes check to return results even if the index hasn’t been used

And more.  Version 17 also added a neat new exclusions table parameter – if you’re the kind of DBA who wants to automate sp_Blitz data collection throughout the environment, but you want to skip certain checks or certain databases, you can do that now.  I haven’t written up full instructions on that, but the basic idea is pretty obvious within reading the first 100-200 lines of the code.

As always, you can get the latest version and instructions from http://www.BrentOzar.com/blitz/.  If you’d like to contribute code, you can email us at Help@BrentOzar.com.  I’m still working through a backlog of about 20 more new check contributions, and we’ve got some fun stuff coming – including a thank-you page listing the contributors and their work.

I don’t blog every release of sp_Blitz (we pushed v15 out silently with a few bug fixes) but we added a lot of improvements and fixes in this version – and by we I mean you.  After I blogged about v14′s release earlier this week, that encouraged a lot of people to come out of the woodwork and contribute code.  I’m still going through all the submissions and adding ‘em in, but I’m pushing this one out the door now because it’s got some cool stuff:

• Chris Fradenburg @ChrisFradenburg http://www.fradensql.com added check 81 for non-active sp_configure options not yet taking effect and improved check 35 to not alert if Optimize for Ad Hoc is already enabled.
• Rob Sullivan @DataChomp http://datachomp.com suggested to add output variable @Version to manage multiple-server installations.  This way you can query all your servers and get back what version they currently have installed.
• Vadim Mordkovich added check 85 for database users with elevated database roles like db_owner, db_securityadmin, etc.
• Vladimir Vissoultchev rewrote the DBCC CHECKDB check to work around a bug in SQL Server 2008 & R2 that reports dbi_dbccLastKnownGood twice.
• 

  Bear Blitzes Brent from Behind

  
  We added checks for database snapshots, stored procs with WITH RECOMPILE in the source code, Agent jobs with SHRINKDATABASE or SHRINKFILE in the steps, and a check for databases with a max file size set.
• We added @CheckServerInfo perameter default 0. Adds additional server inventory data in checks 83-85 for things like CPU, memory, service logins. None of these are problems, but if you’re using sp_Blitz to assess a server you’ve never seen, you may want to know more about what you’re working with. (Kendra’s idea!)
• Tweaked check 75 for large log files so that it only alerts on files > 1GB.
• Fixed a few case-sensitivity bugs.
• Added WITH NO_INFOMSGS to the DBCC calls to ease life for automation folks.  I was surprised by the number of requests we got for this – turns out a lot of people are doing widespread patrols of their servers with sp_Blitz!
• Works with offline and restoring databases. (Just happened to test it in this version and it already worked – must have fixed this earlier.)

If you’d like to contribute code, contact us.  Pro tip: if your code is written in a way that I can just copy/paste into sp_Blitz, it’ll get published a lot faster.  I get a lot of contributions that are various DMV queries, but if I have to rework it to handle multiple databases simultaneously, work differently for 2005/2008/2012, and handle case-sensitive collations, then it takes me much longer to implement (sometimes months).

You can download sp_Blitz now and stop getting surprised by your SQL Server’s hidden past.  Enjoy!