Header

Thursday, February 20, 2014

Monitoring if my databases are online

Usually my customers are monitoring if their servers are online, but in the case of SQL Server, monitoring if the databases are online is just as vital.

This little script checks if the databases are not online and alert you via email.



IF EXISTS (
   Select Name,state_desc 
   From Sys.databases 
   where state_desc <> 'ONLINE'
    )
BEGIN
 DECLARE @tableHTML  NVARCHAR(MAX) ;

 SET @tableHTML =
  N'<H1>OFFLINE Databases</H1>' +
  N'<table border="1">' +
  N'<tr><th>Database Name</th><th>State</th></tr>' +
  CAST ( ( SELECT td = Name, ' ',
      td = state_desc, ' '
      FROM Sys.databases 
      Where state_desc <> 'ONLINE'
      FOR XML PATH('tr'), TYPE 
  ) AS NVARCHAR(MAX) ) +
  N'</table>' ;

 EXEC msdb.dbo.sp_send_dbmail @recipients='you@yourcompany.com',
  @subject = 'OFFLINE Databases',
  @body = @tableHTML,
  @body_format = 'HTML' ;
END



Create a simple job that runs the above script a often as you want - eg. every 10 minutes.

No comments: