Let’s jump in the time machine and go back to April 2010. I attended my first SQL Saturday, in Chicago, IL. One of the speakers was a blogger I had been reading, named Brent Ozar. He was showing off a script he’d written, sp_Blitz, to diagnose issues with SQL Servers. I loved the script and have used it at various times throughout my career.
Running sp_Blitz is like sending your SQL Server instance to the doctor. It checks your vitals – it asks if backups are being taken. It tells you who has sysadmin privileges. It checks security. It looks at what non-default options are turned on. It finds tables without clustered indexes. It warns you if tables have triggers on them. It’s a comprehensive overview of which best practices could be implemented.
Making sp_Blitz Better
Throughout the years, the SQL Server community has contributed to making sp_Blitz better (because the community is awesome like that). Suggestions have been submitted to check for mis-matched collations, looking for database owners, and even to fix spelling errors!
It’s a great give and take project. There have been over 10,000 downloads of it so far! Brent gave it to the community, they have used it, and they’ve given suggestions to make it better.
sp_Blitz Results Can Now Be Shown As A Report!
In May of 2012, I joined Brent Ozar PLF! Now, I’m able to make my own contribution to sp_Blitz. I didn’t know this when they hired me, but all those years of playing with SQL Server Reporting Services came in handy right away. I’ve created a SQL Server Management Studio custom report to display the results of sp_Blitz!
You’ve been looking at results displayed in this format:
Now, when you install the custom report, you can view your results in this format:
You can download the report here.
Instructions to install the sp_Blitz custom report:
- Open SQL Server Management Studio and connect to an instance.
- Right-click the instance name and select Reports > Custom Report. (This will create the appropriate folder – C:\Users\username\Documents\SQL Server Management Studio\Custom Reports.)
- Copy the .rdl file to the Custom Reports folder just created.
- In SSMS, right-click the instance name, select Reports > Custom Report > sp_blitz.
- If you see a Warning, “You are about to run a custom report….”, click Run.
Running a custom report by accessing it through the instance name will make the report execute in the context of the master database. If you create stored procedures in a different database, simply right-click the database name and select Reports > Custom Reports to execute it in the context of that database.
Interested in learning more? I’ve made a short video that explains how to run and use the report!