You have a monitoring tool, but you’ve set up an email rule to dump all the alerts into a folder.
You’re not particularly proud of that, but it is what it is. You’re just tired of the spam.
But when you get in in the morning, you want a simple screen that shows you if anything is really and truly broken in your environment.
Step 1: set up a list of registered servers or a Central Management Server. This lets you execute a single query across multiple servers.
Step 2: start a group query. Right-click on the group of servers, and click New Query.
Step 3: run sp_Blitz @IgnorePrioritiesAbove = 50, @CheckUserDatabaseObjects = 0. This gets you the fast headline news, especially when used with the improved priorities in the latest version in our SQL Server download kit:
You’ll discover when:
- Corruption has been detected
- Databases in full recovery mode aren’t getting log backups
- You’re running a known dangerous build of SQL Server
- Poison waits have struck
- And much more
On Monday mornings, start here. I know, you’re probably not going to find anything, because your servers are in flawless shape and nothing ever goes wrong.
But just in case….
15 Comments. Leave new
Where does sp_blitz need to exist to run it from a Local Server Group or Central Management query?
Hi,
sp_blitz will need to be created on the servers that are part of the CMS. When a query is executed from a CMS, they are actually executed on the individual servers.
Sorry if this is a noob question but is there a way to schedule this using something like a SQL Agent job and sp_send_dbmail? It would be great to have the results delivered directly to email each day/week…
Aaron – no, we don’t support database mail of the query results just yet. For now, you have to run it manually, or build something with PowerShell to run across multiple servers and email yourself the output.
Wow – quick response! Thanks, I’ll look into it. Keep up the great work.
Hahaha, you’re welcome.
What we do to get an email report…
its an HTML table. SpBlitz runs daily and all results are shipped to a central server. Then cross ref the CMS server list and pull out just the prod servers and high priority items in a view.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT
vsrps.ServerName AS ‘td’,”,
vsrps.Finding AS ‘td’,”,
vsrps.GroupName AS ‘td’,”,
vsrps.Priority AS ‘td’
FROM dbo.vSpblitzReportProdServers vsrps (a view that has the top issues by server)
ORDER BY vsrps.Priority
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))
SET @body =’SQL Server Status Report
SP Blitz Full Report
ServerName Finding Group Priority ‘
SET @body = @body + @xml +”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘myProfile’,
@body = @body,
@body_format =’HTML’,
@recipients = ‘The SQL Admins’,
@subject = ‘SP_Blitz Report’ ;
Hi,
First I like to say thanks for all the work you do for the SQL Server community. It is great and I have learned a lot from you guys.
I wonder if I may suggest an improvement for this this. I have a lot of instances in registered servers and many of them are secondary cluster replica nodes in AlwaysOn Availability Group environment. I have backup configured to be performed from only primary replica cluster node and because of that when I run the command above I get a lot of headline news from my secondary replica nodes about “Full Recovery Mode w/o Log Backups” and “Backups Not Performed Recently”. Could you implement intelligence to check if the databases are part of availability group and then if backup should be done or not. And only report health news when needed. Does it make any sense?
Peter – how would we gather that information? Imagine a scenario where backup jobs move around from replica to replica based on fail overs, time of day, etc. for example, say you’ve had full backups every day for weeks on one replica, and then you fail over. If you run sp_Blitz now, and the backup isn’t on the right server, how will we know?
Very good point Brent.
I guess it was more of a wishful thinking from my part as in my case this check against registered servers for all my instances the result makes so much noise about backups that it is very hard to find the actual backup issues when you have such a huge amount of databases.
My thoughts were that perhaps there could be a new parameter like for example @CheckBackupPreferenceOnly with true, false value, meaning that if TRUE I would only be checking backup issues based on Availability Group Backup Preference.
For checking database role I thought that the check could to return role status from sys.dm_hadr_availability_replica_states and for checking backup preference sys.fn_hadr_backup_is_preferred_replica could be used.
Right – that only works if you never fail over. 😀
Hi,
how about registering the Listener name in the CMS, instead of the node names. this way, if the backups are configured for primary nodes only, then even with fail-overs the primary will always be checked for backups. problem would be if there where other databases on the nodes not part of AlwaysOn groups.
Ashneet – that check will fail as soon as you fail over from one node to another. The new primary node won’t have any of the backup history.
It also doesn’t work if you’ve offloaded backups to a secondary.
Just thinking aloud, perhaps you could use create a simple SSRS report and use a subscription for emailing (If you already use SSRS)?
To make this “dirt simple”, create the server group, connect a query to the group, create a database on every server in the group (perhaps call it “dba”). Install all the Ozar spiffy stuff into this db. Continue on from above. You didn’t just dump it all into master did you?