Get Notified when a database status changes
Introduction
This article is based on a code that can be used to send notifications to users when the state of a database changes from Online. There are many third party monitoring solutions which generates tickets on you ticketing system or send email notifications when the state of a database is not Online. However for small environments and where there is no monitoring solution this code can be used. This can be used in a SQL Agent job and then scheduled to run every 1 minute. So when the job runs it will send email if the state of the database is not online. This is quick way to get notified when the state of a database changes with no other monitoring solution.
Background
This article is based on a forum post "How to get alerted or notified when db goes offline". The piece of code in the forum post sends an email if the database state of a particular database changes. This code is an improvement of the same allowing to check multiple databases.
How to run the code
The code uses template parameters to input parameters into the code. You need to copy the code and paste it into a new query window in SSMS. Once done you need to press CTRL + SHIFT + M which will pop-up the parameter input dialogue box where we can input the parameters. Once done the query can be used in a job or manually executed.
The parameter Database_Name accepts the names of databases. If you want to check multiple databases then you need to enter them separated with a comma (,). If you want to check all databases then you can specify 'All'. If you want to only check a single database then just enter the name of the database.
Profile_Name parameter accepts the name of the database mail profile you want to use to send notifications.
Recipient_Email parameter accepts the email address of users whom you want to send notifications. Multiple email addresses needs to be semi colon(;) delimited.
Lastly Email_Subject parameter is for you to specify what subject you would like to have in the notification email.
The code
The first part of the code is mainly the declaration part. Here the variables and table variables are declared.
01.declare @State varchar(10)
02.declare @Database_Name varchar(100)
03.declare @Server_Name varchar(100)
04.declare @EmailBody varchar(100)
05.declare @DBName varchar(max)
06.declare @Html nvarchar(max)
07.set @State = ''
08.set @Database_Name = '<Database_name, varchar(max), ALL>'
09.Declare @DatabaseState table (DBName varchar(100),DBState varchar(50))
The next part of the code checks the parameter Database_name and then checks sys.databases for the status if the parameter is 'All' and inserts into the table variable. If it is not 'All', the code then dissects each database name and then updates the table variable with its status.
01.if @Database_Name = 'All'
02. begin
03. insert into @DatabaseState
04. select name, state_desc
05. from sys.databases
06. end
07.else
08. begin
09. set @DBName = @DBName + ','
10. While LEN(@DBName) > 0
11. begin
12. Insert into @DatabaseState (DBName)
13. select substring (@DBName,0,charindex(',',@DBName))
14. Set @DBName = SUBSTRING (@DBName,charindex(',',@DBName)+1,LEN(@DBName))
15.
16. end
17. Update a
18. set a.DBState = b.state_desc
19. from @DatabaseState a inner join sys.databases b
20. on a.DBName = b.name
21. end
The last part of the code checks if there are any databases with status which is not online and then based on that sends an email. The email is sent in an HTML format and will contain the list of databases and their status. Only the databases with status other than Online will be there in the email.
01.if (select COUNT(*) from @DatabaseState where DBState <> 'Online') > 0
02. begin
03. SET @HTML =
04. N'<H1>Database Status</H1>' +
05. N'<H1>Status of Databases that are not Online</H1>' +
06. N'<table border="1">' +
07. N'<tr><th>Database Name</th><th>Database State</th>' +
08. CAST ( ( SELECT td = DBName, '',
09. td = DBState, ''
10. FROM @DatabaseState where DBState <> 'Online'
11. FOR XML PATH('tr'), TYPE
12. ) AS NVARCHAR(MAX) ) +
13. N'</table>' ;
14. EXEC msdb.dbo.sp_send_dbmail
15. @profile_name = '<Profile_Name, varchar(100), Profile_Name>',
16. @recipients = '<Recipient_Email, varchar(200), Recipient_Email>',
17. @body = @Html,
18. @body_format = 'HTML',
19. @subject = '<Email_Subject, varchar(200), Email_Subject>' ;
20.
21. End
Please find the link to download the full code from the Gallery - Link