sp_Blitz is a free health check script that gives you a prioritized list of things to fix on your SQL Server. Here’s what the output looks like:
Most of the time, you’ll want to work through the warnings in the order that they show up in the results, in priority order.
How to Skip Checks Across Your Estate
But sometimes, there are checks or databases that you want to skip. For example, say this server that we’re working with here is an Availability Group primary, but I’ve chosen to offload my backups to the AG secondary. Since sp_Blitz can’t necessarily connect to all your AG replicas and figure out where you’re doing your backups, you may want to skip the backup checks on this server.
To do that, create a table to hold the list of checks you want to skip, and put rows into it:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE dbo.BlitzChecksToSkip ( ServerName NVARCHAR(128), DatabaseName NVARCHAR(128), CheckID INT); INSERT INTO dbo.BlitzChecksToSkip (ServerName, DatabaseName, CheckID) VALUES ('SQL2019PRIMARY', NULL, 1); |
The CheckID column refers to the list of sp_Blitz checks by priority. You can also scroll to the right in sp_Blitz’s results and look at the CheckID column to see the number of the one you want to skip.
- If you want to skip a check on all servers, all databases, then leave the ServerName & DatabaseName null.
- If you want to skip a check on one server, but all the databases on it, put in its ServerName, but leave DatabaseName null.
- If you want to skip a check on a particular database name, but all of your servers, populate the DatabaseName, but leave the ServerName null. (Like if you want to skip checks on all of your ReportServer databases.)
- If you want to skip ALL checks on a particular database, populate the DatabaseName (and/or ServerName), but leave CheckID null.
Then, when you run sp_Blitz, point to your skip-checks table:
|
1 2 3 4 |
sp_Blitz @SkipChecksDatabase = 'DBAtools', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzChecksToSkip'; |
And presto, no more backup warnings:
As of the August 2020 release, you can also centralize this skip checks table by putting it in a central location, setting up a linked server pointing to your central location, and then using the @SkipChecksServer parameter:
|
1 2 3 4 5 |
sp_Blitz @SkipChecksServer = 'ManagementServerName', @SkipChecksDatabase = 'DBAtools', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzChecksToSkip'; |
How to Centralize Results Across Your Estate
As long as you’re centralizing your management of checks to skip, you can also centralize the results. sp_Blitz has parameters to write the output to a database, and it works across linked servers, too:
|
1 2 3 4 5 6 7 8 9 |
sp_Blitz @SkipChecksServer = 'ManagementServerName', @SkipChecksDatabase = 'DBAtools', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzChecksToSkip', @OutputServerName = 'ManagementServerName', @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults'; |
sp_Blitz will create the table if it doesn’t already exist (but the linked server, database, and schema have to already exist.)
Set that up in a daily Agent job or run it centrally from PowerShell, and you can have all your servers phoning home into one table on a regular basis. You can query the results to slice & dice ’em by priority, or find out what problems are new in your environment today as opposed to yesterday:
The output table has the same results as sp_Blitz, but adds two columns: ServerName and CheckDate, so you can query data by server or by most recent check dates.
13 Comments. Leave new
Fabulous! The blitz is incredible already, but this makes it even more usefule. Super work, Brent. Much appreciated.
You’re welcome, my pleasure!
The linked servers and a pull from a central server is how I get the Blitz results in to a single report. Smae method I’ve used for a long while now to pull backup, DBCC, etc. info.
Yeah, I love the pull from linked servers approach, but it doesn’t work if not all SQL Servers are online, or if the central server isn’t allowed to read from the hub servers.
Great as always. Thanks Brent!
Well, if the server isn’t online, then I probably don’t care about it. If it is up, but not accessible due to security restrictions, then that is another matter. That situation can be surprising because sometimes the server is behind an iron wall, but wide open after that so I can use something like xp_cmdshell/BCP to export the table to a file and FTP it out or send it to an Azure container, etc. Other times, the server is so locked down you can’t get anything out of it. In those cases I kick the work back to the customer.
BTW,
I dig the notification email. Brace yourself!
Hahaha, thanks!
who else has set up a tools database flippantly on the c:\ drive, intend on making a cleanup step to remove old blitz logs, forgot about it, and then found the C:\ drive full with a 30 gb tools database a couple years later?
Do you have any guidance on configuring this as an MSP to report to a central DB that covers all of our customers?
I have an idea, but it isn’t really pretty. I’m assuming that you don’t have linked servers, direct access, etc. and VPN, jumpbox or something like a Bastion server is used for access to customer environments. If as an MSP you are using O365 you could set up flows. For each managed server you could set up an agent job that sends the results as a CSV or Excel attachment to a specific address or subject. The flow could detect this and save the attachment off to a repository and from there you could import the results.
What I’ve written above leaves out a lot of detail, and some serious planning would be needed to make this manageable. The source and repository table would have a column for customer and server name so the flow doesn’t need to do any of that. I haven’t done this for Blitz, but I have for other purposes and it can work, it just isn’t ideal.
Working to use the centralized storage path and initially could not get the sp_Blitz to output from one server to the primary so I created a linked server which seemed to get over that hurdle but now I’m getting the following error:
Xml data type is not supported in distributed queries. Remote object ‘IROWSET’ has xml column(s).
and I’m not real sure how to address this particular issue. Any suggestions for me?
Whenever you’ve got questions about a stored proc, make sure to check out its parameters. There might be a helpful one that, say, allows you to @OutputXMLasNVARCHAR. 😉
Copy that. Thank you for the heads up.