sp_Blitz to collect information from SQL Server estate in a centralized server

If you are taking over SQL Server(s) from someone or your client requested a quick health check of a SQL Server then the first thing that comes to your mind is “sp_Blitz”. Thanks to Brent Ozar. I don’t need to explain anything about sp_Blitz as it is industry standard.

You can either use it on the fly or run it every day on all of your SQL Servers. If you want to run it on all your SQL Servers and want to store the results in a single table, on a central SQL Server along with server names and date/time, you might think of the following:

• Linked Servers
• Central Management Server
• SQL Multi Script from Red Gate Software

OR

• Use the following PowerShell script

You can use thisPowerShell script in a SQL Agent job to deploy and run sp_Blitz or you can run this manually as and when required. The script takes three input parameters:

SQLInstances – List of SQL Server Instances where you want to deploy and run sp_Blitz. You can use an array or a file to input SQL Server Instances

ResultsInstance – Name of the central SQL Server where you want to store the results

ResultsDB – Name of the database on the ResultsInstance where you want to create the results table

I have removed carriage return, line feeds and apostrophes from sp_Blitz’s result so please let this script create the stored procedure for you otherwise you will get errors.

This script deploy sp_Blitz on all servers, every time it runs. I kept it like this to avoid any errors if you add new servers and forget to deploy sp_Blitz using this script. If you want to remove this overhead then delete everything between “Deploy sp_Blitz” and “Get sp_Blitz” comments after the first run.

Please read the “Disclaimer” page before using this script.