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:
Post a Comment