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
- 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.
Leave a Reply
2 Comments on "sp_Blitz to collect information from SQL Server estate in a centralized server"
Hello Muhammad, thank you for providing this solution. I’ve downloaded the PS script and deployed it on my test server, however, I ran into an issue, hoping you can steer me in the right direction. I’ve modified the 3 input parameters but I do not see the results in the sp_Blitz_Results table. Am I missing something? Thank you!
I’ve figured out the problem. I was connecting to the ResultsInstance via VPN on my laptop. Once I RDP’d into the ResultsInstance, I was able to run the PS script and see the results in sp_Blitz_Results table.