sp_Blitz® v16: Snapshots, Recompiles, ShrinkDB, and More

SQL Server

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.
    • I'll give you fifteen minutes to stop that.
      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!

Previous Post
sp_Blitz® v14 Adds VLFs, DBCC, Failsafe Operators, and More
Next Post
Instant Index Insight: How to Use sp_BlitzIndex® (video)

9 Comments. Leave new

  • Awesome enhancements Brent ! I have written a similiar solution for my current company with bunch of other stuff to check. We use my solution for Health checking for all servers.

    Keep up the good work !!

  • Nice picture. I’m hoping “blitzes” isn’t a euphemism…

  • I know the answer, but the message looks like not to be clear enough for me
    1 Performance Memory Dangerously Low http://BrentOzar.com/go/max Although available memory is 217 megabytes, only 20478megabytes of memory are present. As the server runs out of memory, there is danger of swapping to disk, which will kill performance. NULL NULL 51

  • Brent,

    The @CheckServerInfo input parameter does not seem to do anything. A search of the code only shows it being declared, a comment about what it does, and in the sample call. Other than that I don’t see it referenced or used in the code.

    Incredible procedure. I have used earlier version with fantastic results. Thanks again for taking the time and effort to create and share this.


  • Hi Brent,

    Just downloaded sp_Blitz v17. The check for elevated database permissions seems to have been removed from this version??



Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.