sp_Blitz® Change Log
Interested in the sordid history of our free sp_Blitz® SQL Server health check script? Here’s the awesome users who’ve contributed in making our lives easier:
Changes in v52 – June 2, 2016:
- SQL Server 2016 compatibility. 2016 RTM ships with some questionable database-level options like heaps in DWDiagnostics, target recovery time changed in the DW* databases, and l_certSignSmDetach as a new default sysadmin login, so ignoring those.
- If databases have an old compatibility level that does not support CTEs then @CheckUserDatabaseObjects is set to 0 to avoid problems with current checks. Get on the current compat level, Grandpa.
Changes in v51 – May 18, 2016:
- Thomas Rushton added a check for dangerous third-party modules. (179) More info: https://support.microsoft.com/en-us/kb/2033238
- New check for snapshot backups possibly freezing IO. Looking for 50GB+ backups that complete in under 60 seconds. (178)
- If there are 50+ user databases, you have to turn on @BringThePain = 1 in order to do @CheckUserDatabaseObjects = 1. (Speeds up sp_Blitz on servers with hundreds or thousands of databases.)
- Reprioritized a bunch of checks, like moving security warnings down to priority 230, so that you can use @IgnorePrioritiesAbove = 50 better.
- And bug fixes. Nothing cool, just boring fixes.
Changes in v50 – April 8, 2016:
- Fixed bug in check ID 2 that would fail on a database with multiple log files, that also has not had a backup in two weeks.
Changes in v49 – April 6, 2016:
- Added Amazon RDS compatibility by skipping several checks in RDS (where you can’t access MSDB, change sp_configure settings, reconfigure TempDB, etc).
Changes in v48 – March 20, 2016:
- Julie Citro massively improved the check for stored procedures with a
recompile hint in them. (78)
- These new checks brought to you by Erik Darling:
- Multiple Extended Events sessions running (176)
- Startup parameter -x in use, disabling monitoring (177)
- TempDB has >16 data files (175)
- Along with some changes:
- Improved full text repopulation check to ignore change_tracking_state_desc AUTO (113)
- DBCC CHECKDB checks now ignore TempDB (68)
- Poison wait times are now reported in Days:Hours:Minutes, and are only reported if they exceed 60 seconds (instead of 5)
- The missing log backups alert now includes the log file size.
- Trace flag alerts now include more details about dangerous trace flags.
- Some per-database checks were running even when @CheckUserDatabaseObjects was set to 0. (Performance tuning sp_Blitz for thousands of databases.)
- The high number of cached plans warning (161) reported the wrong limit for cached plans in the detail message.
Changes in v47 – March 12, 2016:
- These new changes brought to you by Erik Darling:
- Locked pages in memory (check 166)
- Agent currently offline (167)
- Full text daemon offline (168)
- SQL Server running as NT Service rather than an AD account (169)
- Agent running as NT Service rather than an AD account (170)
- Memory dumps in the last year (171)
- Windows version (172)
- Dev or Evaluation Edition in use (173)
- Buffer Pool Extension enabled (174)
Changes in v46 – January 6, 2016:
- Thomas Franz suggested that the severity level be 10, not 20, when sp_Blitz
fails to install due to a SQL 2000 compat level issue.
Changes in v45 – January 6, 2016:
- Oops! Fixed a SQL 2005 compatibility bug.
Changes in v44 – January 5, 2015:
- Reorganized and reprioritized a lot of the checks.
- Added check 165 for too much free memory. (Sounds like a good thing, but turns out it can indicate queries with inaccurately huge memory grants.)
- Tobias Ortmann reported a bug in missing new default configs in SQL 2014.
- Upped “slow storage” warnings to 200ms for reads, 100ms for writes, and only throw warnings on files with more than 100,000 reads or writes. Yes, those thresholds are horrifically high, but that is the point.
- “Old compat level” warning now only fires for level 90 and older, not a lower compat level than model. With the new Cardinality Estimator, we have seen a lot of people running at an older-than-model level, and that is fine – it should not generate an “old” warning.
- Bug fixes.
Changes in v43 – December 11, 2015:
- Ken Nelson identified a bug in checks 97 & 154 for the skip-checks table.
- Removed not-really-useful checks for endpoints (9), extraneous backups for ReportServerTempDB (127), Agent XPs enabled (1006), Database Mail XPs enabled (1015).
- Backup compression warning (116) now checks not just the default config setting, but also to see if uncompressed full backups have been done in the last 2 weeks.
- Default file growth size checks for 1MB or percent growths (82, 158) now include the full file name and size in the details column, and only alert for files over 1GB in size.
- Log file larger than data files check (75) now reports the log file size in the details column.
- Tables in msdb check (28) now ignores Database Tuning Advisor tables.
- Untrusted foreign keys check (48) now ignores master, model, msdb, ReportServer, ReportServerTempDB.
- User-created stats check (122) now reports the number of user-created stats in the details column.
- Bug fixes.
Changes in v42 – September 7, 2015:
- Added check 163 for SQL Server 2016 databases with Query Store disabled.
- Added a few ignorable waits.
- Do not say no-significant-waits-found if we detected poison waits.
- Stop people from trying to install it in SQL Server 2000 compat mode.
- Bug fixes.
Changes in v41 – June 18, 2015:
- Added check 162 for CMEMTHREAD waits on servers with >= 8 logical processors per NUMA node.
- Added check 159 for NUMA nodes reporting dangerously low memory in sys.dm_os_nodes.
- Added check 161 for a high number of cached plans per KB 3026083.
- Fixed a bug in the SkipChecks routines. Reported by Kevin Collins.
- Backup-to-same-drive-as-databases check (93) now includes the number of backups that were done so you can tell if it was a one-off problem, or if all backups are going to the wrong place.
- Bug fixes and improvements.
Changes in v40 – April 27, 2015:
- Added check 158 for 1MB growth sizes on databases over 10GB. Probably time to up that growth size. Contributed by Henrik Staun Poulsen.
- Added check 159 for queries with more than 50 execution plans in cache, an indicator that it is not parameterized properly.
- Fixed check 97 that said Data Center Edition was subject to CPU and memory limits, which is not true. It is only subject to wallet limits. Reported by Brad Nelson.
- Fixed checks 106, 150, 151 to be skipped when the default trace file has disappeared. Coded by Steve Coles.
- Fixed check 1, the VERY FIRST CHECK IN THE SCRIPT, which had a bug when catching databases that had never been backed up. Sure, there was a workaround in the next statement, but Julie Citro spotted the bug and made it right. First check, people. All of you who ever read this code, Julie Citro is officially a better code reviewer than you.
- Skip backup checks on offline databases.
- For order and join hints, raised threshold to 1000 instead of 1.
Changes in v39 – February 16, 2015:
- Added @OutputType option for NONE if you only want to log the results to a table. (For Jefferson Elias.)
- Bug fixes and improvements. (Thanks, Nathan Sunderman.)
Changes in v38 – November 20, 2014:
- Added check 157 for dangerous builds of SQL Server that are affected by MS Security Bulletin MS14-044.
- Added current date to output as check 156, priority 254. Requested by Denise Crabtree, who runs sp_Blitz on a regular basis and saves the results in a spreadsheet. Yay, Denise!
- Bug fixes and improvements to wait stats checks.
Changes in v37 – November 19, 2014:
- Added wait stats checks when @CheckServerInfo = 1. Check 152 looks for waits that have accounted for more than 10% of minimum possible wait time. If your 4-core server has been up for 40 hours, that is 160 hours of potential wait time (and of course it could be much higher when multiple queries are stacked up on each core.) In that case, we only alert on waits that have accounted for at least 16 hours of wait time. We are trying to avoid false-alarming when servers are sitting idle.
- Added check 154 for 32-bit SQL Servers.
- Added check 155 for sp_Blitz versions more than 6 months old.
Changes in v36 – October 5, 2014:
- Added non-default database configuration checks looking at sys.databases
as checks 131-144. Catches things like delayed durability, forced params.
- Added check for long file growths from the default trace, 151.
- Added check for serious errors in the default trace, 150.
- Added Hekaton memory use and transaction error checks 145-147.
- Added checks for database files on network shares or Azure, 148-149.
- Added server name row in output when @CheckServerInfo = 1.
- Moved contributions to support.brentozar.com.
- Check 78 for stored procs with RECOMPILE now ignores sp_Blitz%.
Changes in v35 – June 18, 2014:
- John Hill fixed a bug in check 134 looking for deadlocks.
- Robert Virag improved check 19 looking for replication subscribers.
- Russell Hart improved check 34 to avoid blocking during restores.
- Added check 126 for priority boost enabled. It was always in the non-default configurations check, but this one is so bad we called it out.
- Added checks 128 and 129 for unsupported builds of SQL Server.
- Added check 127 for unneccessary backups of ReportServerTempDB.
- Changed fill factor threshold to <80% to match sp_BlitzIndex.
Changes in v34 – April 2, 2014:
- Jason Pritchard fixed a bug in the plan cache analysis that did not return results when analyzing for high logical reads.
- Kirby Richter @SqlKirby fixed a bug in check 75 (t-log sizes) that failed on really big transaction log files. (Not even gonna say how big.)
- Oleg Ivashov improved check 94 (jobs without failure emails) to exclude SSRS jobs.
- Added @SummaryMode parameter to return only one result set per finding.
- Added check 124 for Performance: Deadlocks Happening Daily. Looks for more than 10 deadlocks per day.
- Moved check 121 for Performance: Serializable Locking to be lower priority (down to 100 from 10) and only triggers when more than 10 minutes of the wait have happened since startup.
- Changed checks 107-109 for Poison Waits to have higher thresholds, now looking at more than 5 seconds per hour of server uptime. Been up for 10 hours, we look for 50 seconds, that kind of thing.
Changes in v33 – January 20, 2014:
- Bob Klimes fixed a bug that Russell Hart introduced in v32, hahaha. Check 59 was false-alarming on Agent jobs that actually had notifications.
Changes in v32 – January 19, 2014:
- Russell Hart fixed a bug in check 59 (Agent jobs without notifications).
- Added @EmailRecipients and @EmailProfile parameters to send the results via Database Mail. Assumes that database mail is already configured correctly. Only sends the main results table, and it will not work well if you also try to use @CheckProcedureCache. Execution plans will not render in email.
- Fixed a bug in checks 108 and 109 that showed poison waits even if they had 0ms of wait time since restart.
- Removed check 120 which warned about backups not using WITH CHECKSUM. We fell out of love with WITH CHECKSUM – turns out nobody uses it.
- Added check 121 – Poison Wait Detected: Serializable Locking – looking for waits with %LCK%R%. Happens when a query uses a combination of lock hints that make the query serializable.
- Added check 122 – User-Created Statistics In Place. There is nothing wrong with creating your own statistics, but it can cause an IO explosion when statistics are updated.
- Added check 123 – Multiple Agent Jobs Starting Simultaneously. Ran into an issue where dozens of jobs started at the exact same time every hour.
Changes in v31 – December 1, 2013:
- Dick Baker, Ambrosetti Ltd (UK):
- Fixed typos in checks 107-109 that looked for the wrong CheckID when
skipping checks, plus improved performance while he was in there.
- Improved check 106 (default trace file) so that it will not error out
if the user does not have permissions on sys.traces.
- Fixed typos in checks 107-109 that looked for the wrong CheckID when
- Christoph Müller-Spengler @cms4j added check 118 looking at the top queries in the plan cache for key lookups.
- Philip Dietrich added check 119 for TDE certificates that have not been
backed up recently.
- Ricky Lively added @Help to print inline help. I love his approach to it.
- Added check 120 looking for databases that have not had a full backup using the WITH CHECKSUM option in the last 30 days.
Changes in v30 – October 12, 2013:
- Doug Lane @TheDougLane:
- Fixed bug in check 99 for unusual editions so that it doesn’t alert on BI Edition, since that’s the same as Standard Edition.
- Fixed bug in check 32 so that it won’t alert on SSRS ReportServer database triggers, which are actually MS-shipped but aren’t marked with the is_ms_shipped flag.
- Ross Whitehead @RWhitehead99 fixed a bug in check 111 (broken log shipping) so that it doesn’t alert on database mirrors.
- Russell Hart fixed a bug with the @VersionDate format that broke in British English language settings.
Changes in v29 – August 23, 2013
- Added @OutputType = ‘SCHEMA’, which returns the version number and a list of columns for a CREATE TABLE definition for the default outputs. We don’t include the actual CREATE TABLE part because you might want to use a table variable or whatever.
- Added @OutputXMLasNVARCHAR. If 1, then the QueryPlan is outputted as an NVARCHAR(MAX) instead of XML. This helps if you want to insert the sp_Blitz results into a temp table. For instructions, visit: https://www.brentozar.com/blitz/documentation/
Changes in v28 – August 21, 2013
- Tom Meyer improved several backup checks so that they’ll work if the master and msdb databases have different collations, like if someone restores msdb from another server with a different collation. (Please don’t do that.)
- Fixed a bug in the VLF check that added a trailing space in the URL. This broke the PDF output in the Windows app.
Changes in v27 – August 6, 2013
- Whoops! Even more bug fixes in check 114. Thanks, Andy Jarman!
Changes in v26 – August 2, 2013
- Whoops! Improved check 114 to skip SQL Server 2005, since the necessary DMVs don’t exist there. Thanks, Conan Farrell.
Changes in v25 – August 2, 2013
- Andrew Jarman was the first to catch a bug in check 70 for named instances.
- David Todd suggested a tweak to make it easier to deploy this stored proc
- in other databases.
- Added check for Adam Machanic’s make_parallel function (115).
- Added check for basic NUMA config (114).
- Added check for backup compression defaulted to off (116), suggested by David Todd.
- Added check for forced grants in sys.dm_exec_query_resource_semaphores, indicating memory pressure is affecting query performance (117).
Changes in v24 – June 23, 2013
- Alin Selicean @AlinSelicean:
- debugged check 72 for non-aligned partitioned indexes.
- improved check 70 for the @@servername variable.
- Andreas Schubert debugged check 14 to remove duplicate results.
- Josh Duewer added check 112 looking for change tracking.
- Justin Dearing @Zippy1981 improved @OutputTableName to export the results to a global temp table.
- Katie Vetter improved check 6 for jobs owned by <> SA, by removing the join to sys.server_principals and using a function for the name instead.
- Kevin Frazier improved check 106 by removing extra copy/paste code.
- Mike Eastland added check 111 looking for broken log shipping subscribers.
- We added check 110 for memory nodes offline.
- Added check 113 for full text indexes not crawled in the last week.
- Changed VLF threshold from 50 to 1,000. We were getting a lot of questions about databases with 51-100 VLFs, and that’s just not a real performance killer. To minimize false alarms, we cranked the threshold way up. Let’s get you focused on making sure your databases are backed up first.
- Fixed bugs in @SkipChecks tables. Man, there’s no way any of you were using that thing, because it was chock full of nuts.
- Added basic SQL Server 2014 compatibility.
Changes in v23 – June 2, 2013:
- Katherine Villyard @geekg0dd3ss caught bug in check 72 (non-aligned partitioned indexes) that wasn’t honoring @CheckUserDatabaseObjects.
- Paul Olson http://www.SQLsprawl.com wrote check 106 to show how much history is being kept in the default traces, and where they are. Only runs if @CheckServerInfo = 1.
- Randall Stone suggested ignoring ReportServer% databases in the collation checks. Prior versions of the checks were only ignoring default name instances of SSRS.
- Added checks for “poison” wait types: THREADPOOL, RESOURCE_SEMAPHORE, and RESOURCE_SEMAPHORE_QUERY_COMPILE. Any occurrence of these waits often indicates a killer performance issue. Checks 107-109.
- Non-default sp_configure options used to be CheckID 22 for all possible sp_configure settings. Now we use the range 1,000-1,999 for sp_configure. This way, if you’re writing a tool that outputs specific advice for each CheckID, you can get more specific with the advice based on which sp_configure option has been changed.
- Fixed various typos.
Changes in v22 – May 6, 2013:
- Fixed new v21 case sensitivity bug reported by several users.
- Cleaned up some typos in script output.
Changes in v21 – April 25, 2013:
- Easier readability – cleaned up the code with Red Gate SQL Prompt, plus added comments explaining what’s happening.
- Added @OutputDatabaseName, @OutputSchemaName, @OutputTableName. If set, the #BlitzResults table is saved into that. Only outputs the check results, not the plan cache. Suggested by Robbert Hof and Andy Bassitt.
- Alin Selicean @AlinSelicean:
- Added check 100 looking for disabled remote access to the DAC.
- Added check 101 looking for disabled CPU schedulers due to licensing or affinity masking.
- Chris Leavitt coded check 103 looking for virtualization.
- Mike Eastland suggested check 102 for databases in unusual states – suspect,
- recovering, emergency, etc.
- Russell Hart coded check 104 looking for logins with CONTROL SERVER perms.
- We added check 105 looking for extended stored procedures in master.
- Moved temp table creation up to the top of the sproc while trying to fix an issue with offline databases. I like it up there, so leaving it. Didn’t fix the issue, but ah well.
- Moved the old changes to http://www.BrentOzar.com/blitz/changelog/
Changes in v20:
– Randy Knight @Randy_Knight http://sqlsolutionsgroup.com identified a bunch
of checks with duplicate IDs, so a few check IDs changed for uniqueness.
Changes in v19:
– Frank van der Heide and Ken Wilson fixed a bug in @IgnorePrioritiesBelow.
Pushed this out since it’s a critical fix for people using sp_Blitz with
Changes in v18:
– Alin Selicean @AlinSelicean:
– Added check 93 looking for backups stored on the same drive letter as user
database files. Will give false positives if both your user databases
and backup files are mount points on the same drive letter, but hey,
don’t do that.
– Fixed typo in check 88 that wouldn’t run if check 91 was being skipped.
– Improved check 72, non-aligned partitioned indexes, to include the
database name even if the index hadn’t been used since restart.
– David Forck @Thirster42:
– Added check 94 for Agent jobs that are not set up to notify an operator
– Improved check 80 to ignore max file sizes for filestream data files.
– Dino Maric added check 92 for free drive space. (Not an alert, just runs if
you set @CheckServerInfo = 1.) Doesn’t include mount points.
– Eric Alter fixed bug with missing begin/end in check 83.
– Nigel Maneffa fixed a broken link in check 91 for merge replication.
– Added check 86 back in for elevated database permissions.
– Replaced @@SERVERNAME usage with SERVERPROPERTY(‘ServerName’) because in
some cloud hosting environments, these don’t match, and it’s okay.
– Changed database name variables to be NVARCHAR(128). Dang SharePoint.
Changes in v17:
– Alin Selician:
– Fixed bug in check 72 for partitioned indexes that weren’t aligned.
– Suggested check 88 for SQL Server’s last restart.
– Nigel Maneffa:
– Added check 90 for corruption alerts in msdb.dbo.suspect_pages.
– Added check 91 for merge replication with infinite retention.
– Nancy Hidy Wilson @NancyHidyWilson fixed a bug in the sys.configurations
check 22: SQL 2012 changed the remote login timeout setting from 20 to 10.
– Roland Rosso suggested to add parameters for @IgnorePrioritiesBelow and
@IgnorePrioritiesAbove so you can just return results for some types of checks.
– Russell Hart fixed a bug in the DBCC CHECKDB check that failed on systems
using British date formats.
– Stephanie Richter fixed description typo in check 57 for Agent startup jobs.
– Steve Wales caught dupe checkID’s 60, changed one to 87.
– Improved check 78 for implicit conversion so that it checks sys.sql_modules
for the is_recompiled bit flag rather than scanning the source code.
– Added check 89 looking for corruption reports in dm_hadr_auto_page_repair.
– Improved checks 58 and 76 (collation mismatches) to exclude ReportServer and
ReportServerTempDB. Microsoft builds those databases their own special way.
– Improved check 22, the default sys.configurations check, to add new SQL 2012
settings, deal with odd default min server memory settings (we’re seeing
both 0mb and 16mb in the wild as defaults).
– Added @OutputType = ‘CSV’ option that strips commas and returns one field per
row rather than separate fields of data. Doesn’t return the query and query
plan fields since those are monsters.
– Created support for excluding databases and/or checks. This will handled via a table that holds
the servers, databases and checks that you would like skipped. Use the script below (naming the table)to
create the table
CREATE TABLE dbo.Whatever
(ID INT IDENTITY(1,1),
ServerName = ‘MyServer, DatabaseName = NULL, CheckId = NULL – will not check anything on servername MyServer
ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on any server
ServerName = ‘MyServer’, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on MyServer
ServerName = NULL, DatabaseName = NULL, CheckId = 5 – will skip CheckId 5 on all databases
ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = 5 – will skip CheckId 5 on MyDB
The new variables @SkipChecksServer, @SkipChecksDatabase, @SkipChecksSchema and @SkipChecksTable provide
the path to where the new table lives.
Changes in v16:
– Vladimir Vissoultchev rewrote the DBCC CHECKDB check to work around a bug in
SQL Server 2008 & R2 that report dbi_dbccLastKnownGood twice. For more info
on the bug, check Connect ID 485869.
– Chris Fradenburg @ChrisFradenburg http://www.fradensql.com:
– Check 81 for non-active sp_configure options not yet taking effect.
– 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 server installations.
– Vadim Mordkovich:
– Added check 86 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 report dbi_dbccLastKnownGood twice. For more info
on the bug, check Connect ID 485869.
– Added check 77 for database snapshots.
– Added check 78 for stored procedures with WITH RECOMPILE in the source code.
– Added check 79 for Agent jobs with SHRINKDATABASE or SHRINKFILE.
– Added check 80 for databases with a max file size set.
– 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. I do.
– Added checks 83-85 for server descriptions (CPU, memory, services.)
– Tweaked check 75 for large log files so that it only alerts on files > 1GB.
– Changed one of the two check 59’s to be check 82. (Doh!)
– Added WITH NO_INFOMSGS to the DBCC calls to ease life for automation folks.
– Works with offline and restoring databases. (Just happened to test it in
this version and it already worked – must have fixed this earlier.)
Changes in v15:
– Mikael Wedham caught bugs in a few checks that reported the wrong database name.
– Bob Klimes fixed bugs in several checks where v14 broke case sensitivity.
– Seth Washeck fixed bugs in the VLF checks so they include the number of VLFs.
Changes in v14:
– Lori Edwards @LoriEdwards http://sqlservertimes2.com
– Did all the coding in this version! She did a killer job of integrating
improvements and suggestions from all kinds of people, including:
– Chris Fradenburg @ChrisFradenburg http://www.fradensql.com
– Check 74 to identify globally enabled traceflags
– Jeremy Lowell @DataRealized http://datarealized.com added:
– Check 72 for non-aligned indexes on partitioned tables
– Paul Anderton @Panders69 added check 69 to check for high VLF count
– Ron van Moorsel added several changes
– Added a change to check 6 to use sys.server_principals instead of syslogins
– Added a change to check 25 to check whether tempdb was set to autogrow.
– Added a change to check 49 to check for linked servers configured with the SA login
– Shaun Stuart @shaunjstu http://shaunjstuart.com added several changes:
– Added check 68 to check for the last successful DBCC CHECKDB
– Updated check 1 to verify the backup came from the current
– Added check 70 to verify that @@servername is not null
– Typo in check 51 changing free to present thanks to Sabu Varghese
– Check 73 to determine if a failsafe operator has been configured
– Check 75 for transaction log files larger than data files suggested by Chris Adkin
– Fixed a bunch of bugs for oddball database names (like apostrophes).
Changes in v13:
– Fixed typos in descriptions of checks 60 & 61 thanks to Mark Hions.
– Improved check 14 to work with collations thanks to Greg Ackerland.
– Improved several of the backup checks to exclude database snapshots and
databases that are currently being restored thanks to Greg Ackerland.
– Improved wording on check 51 thanks to Stephen Criddle.
– Added top line introducing the reader to sp_Blitz and the version number.
– Changed Brent Ozar PLF, LLC to Brent Ozar Unlimited. Great catch by
Hondo Henriques, @SQLHondo.
– If you’ve submitted code recently to sp_Blitz, hang in there! We’re still
building a big new version with lots of new checks. Just fixing bugs in
this small release.
Changes in v12:
– Added plan cache (aka procedure cache) analysis. Examines top resource-using
queries for common problems like implicit conversions, missing indexes, etc.
– Added @CheckProcedureCacheFilter to focus plan cache analysis on
CPU, Reads, Duration, or ExecCount. If null, we analyze all of them.
– Added @OutputProcedureCache to include the queries we analyzed. Results are
sorted using the @CheckProcedureCacheFilter parameter, otherwise by CPU.
– Fixed case sensitive calls of sp_MSforeachdb reported by several users.
Changes in v11:
– Added check for optimize for ad hoc workloads in sys.configurations.
– Added @OutputType parameter. Choices:
– ‘TABLE’ – default of one result set table with all warnings.
– ‘COUNT’ – Sesame Street’s favorite character will tell you how many
problems sp_Blitz found. Useful if you want to use a
monitoring tool to alert you when something changed.
Changes in v10:
– Jeremiah Peschka added check 59 for file growths set to a percentage.
– Ned Otter added check 62 for old compatibility levels.
– Wayne Sheffield improved checks 38 & 39 by excluding more system tables.
– Christopher Fradenburg improved check 30 (missing alerts) by making sure
that alerts are set up for all of the severity levels involved, not just
some of them.
– James Siebengartner and others improved check 14 (page verification) by
excluding TempDB, which can’t be set to checksum in older versions.
– Added check 60 for index fill factors <> 0, 100.
– Added check 61 for unusual SQL Server editions (not Standard, Enterprise, or
– Added limitations note to point out that compatibility mode 80 won’t work.
– Fixed a bug where changes in sp_configure weren’t always reported.
Changes in v9:
– Alex Pixley fixed a spelling typo.
– Steinar Anderson http://www.sqlservice.se fixed a date bug in checkid 2.
That bug was reported by several users, but Steinar coded the fix.
– Stephen Schissler added a filter for checkid 2 (missing log backups) to look
only for databases where source_database_id is null because these are
database snapshots, and you can’t run transaction log backups on snapshots.
– Mark Fleming @markflemingnl added checkid 62 looking for disabled alerts.
– Checkid 17 typo changed from “disabled” to “enabled” – the check
functionality was right, but it was warning that auto update stats async
was “disabled”. Disabled is actually the default, but the check was
firing because it had been enabled. (This one was reported by many.)
Changes in v8 May 10 2012:
– Switched more-details URLs to be short. This way they’ll render better
when viewed in our SQL Server Management Studio reports.
– Removed ?VersionNumber querystring parameter to shorten links in SSMS.
– Eliminated duplicate check for startup stored procedures.
Changes in v7 April 30 2012:
– Thomas Rushton http://thelonedba.wordpress.com/ @ThomasRushton added check
58 for database collations that don’t match the server collation.
– Rob Pellicaan caught a bug in check 13: it was only checking for plan guides
in the master database rather than all user databases.
– Michal Tinthofer http://www.woodler.eu improved check 2 to work across
collations and fix a bug in the backup_finish_date check. (Several people
reported this, but Michal contributed the most improvements to this check.)
– Chris Fradenburg improved checks 38 and 39 by excluding heaps if they are
marked is_ms_shipped, thereby excluding more system stuff.
– Jack Whittaker fixed a bug in checkid 1. When checking for databases
without a full backup, we were ignoring the model database, but some shops
really do need to back up model because they put stuff in there to be
copied into each new database, so let’s alert on that too. Larry Silverman
also noticed this bug.
– Michael Burgess caught a bug in the untrusted key/constraint checks that
were not checking for is_disabled = 0.
– Alex Friedman fixed a bug in check 44 which required a running trace.
– New check for SQL Agent alerts configured without operator notifications.
– Even if @CheckUserDatabaseObjects was set to 0, some user database object
checks were being done.
– Check 48 for untrusted foreign keys now just returns one line per database
that has the issue rather than listing every foreign key individually. For
the full list of untrusted keys, run the query in the finding’s URL.
Changes in v6 Dec 26 2011:
– Jonathan Allen @FatherJack suggested tweaking sp_BlitzUpdate’s error message
about Ad Hoc Queries not being enabled so that it also includes
instructions on how to disable them again after temporarily enabling
it to update sp_Blitz.
Changes in v5 Dec 18 2011:
– John Miner suggested tweaking checkid 48 and 56, the untrusted constraints
and keys, to look for is_not_for_replication = 0 too. This filters out
constraints/keys that are only used for replication and don’t need to
– Ned Otter caught a bug in the URL for check 7, startup stored procs.
– Scott (Anon) recommended using SUSER_SNAME(0x01) instead of ‘sa’ when
checking for job ownership, database ownership, etc.
– Martin Schmidt http://www.geniiius.com/blog/ caught a bug in checkid 1 and
contributed code to catch databases that had never been backed up.
– Added parameter for @CheckProcedureCache. When set to 0, we skip the checks
that are typically the slowest on servers with lots of memory. I’m
defaulting this to 0 so more users can get results back faster.
Changes in v4 Nov 1 2011:
– Andreas Schubert caught a typo in the explanations for checks 15-17.
– K. Brian Kelley @kbriankelley added checkid 57 for SQL Agent jobs set to
start automatically on startup.
– Added parameter for @CheckUserDatabaseObjects. When set to 0, we skip the
checks that are typically the slowest on large servers, the user
database schema checks for things like triggers, hypothetical
indexes, untrusted constraints, etc.
Changes in v3 Oct 16 2011:
– David Tolbert caught a bug in checkid 2. If some backups had failed or
been aborted, we raised a false alarm about no transaction log backups.
– Fixed more bugs in checking for SQL Server 2005. (I need more 2005 VMs!)
Changes in v2 Oct 14 2011:
– Ali Razeghi http://www.alirazeghi.com added checkid 55 looking for
databases owned by <> SA.
– Fixed bugs in checking for SQL Server 2005 (leading % signs)