Thursday, 13 December 2012

Monitoring Solution to check the Database Status in SQL Server 2012





I have explained in another article about different database states in SQL Server 2012[http://www.dbtechnosolutions.com/2012/12/different-database-states-in-sql-server.html]. Now how DBAs or end-user can be notified automatically, if database state changed from one to another due to some issue. It is really helpful for DBAs\Users, if we have any automated process in case of database states change.

Here I have provided monitoring solution which can be scheduled through SQL Agent or in command prompt. This will notify to end-users, if any changes in the database state.

Specify the list of end users [separated by comma], to whom it has to be notified in case of database state changes.

--**********************************************************************
--Database state:
--0 = ONLINE
--1 = RESTORING
--2 = RECOVERING
--3 = RECOVERY_PENDING
--4 = SUSPECT
--5 = EMERGENCY
--6 = OFFLINE
--***********************************************************************

DECLARE @DBName VARCHAR(100)
DECLARE @State INT
DECLARE @State_Desc VARCHAR(100)
DECLARE @receipient_Address VARCHAR(100)
DECLARE @profile VARCHAR(100)
DECLARE @msg VARCHAR(1000)

SET @receipient_Address = '<Specify list of end users separated by comma'
SET @profile = '<Specify the profile Name'

DECLARE db_cur CURSOR LOCAL FAST_FORWARD
FOR
                        SELECT
                                    name,
                                    state,
                                    state_desc
                        FROM
                                    sys.databases
                        WHERE
                                    state != 0;
OPEN db_cur
FETCH NEXT FROM db_cur INTO @DBName, @State, @State_Desc
WHILE @@FETCH_STATUS=0
BEGIN
SET @msg = 'URGENT - Database ' + @DBName + ' is in ' + @State_Desc + ' state on ' + @@SERVERNAME
            IF @State = 1 AND @State_Desc = 'RESTORING'
                        BEGIN
                                     EXEC msdb.dbo.sp_send_dbmail
                                                  @recipients = @receipient_Address,
                                                  @profile_name = @profile,
                                                  @body = @msg,
                                                  @subject = @msg;
                        END
            ELSE IF @State = 2 AND @State_Desc = 'RECOVERING'
                        BEGIN
                                    EXEC msdb.dbo.sp_send_dbmail
                                                  @recipients = @receipient_Address,
                                                  @profile_name = @profile,
                                                  @body = @msg,
                                                  @subject = @msg;
                        END
            ELSE IF @State = 3 AND @State_Desc = 'RECOVERY_PENDING'
                        BEGIN
                                    EXEC msdb.dbo.sp_send_dbmail
                                                  @recipients = @receipient_Address,
                                                  @profile_name = @profile,
                                                  @body = @msg,
                                                  @subject = @msg;
                        END
            ELSE IF @State = 4 AND @State_Desc = 'SUSPECT'
                        BEGIN
                                    EXEC msdb.dbo.sp_send_dbmail
                                                  @recipients = @receipient_Address,
                                                  @profile_name = @profile,
                                                  @body = @msg,
                                                  @subject = @msg;
                        END
            ELSE IF @State = 5 AND @State_Desc = 'EMERGENCY'
                        BEGIN
                                    EXEC msdb.dbo.sp_send_dbmail
                                                  @recipients = @receipient_Address,
                                                  @profile_name = @profile,
                                                  @body = @msg,
                                                  @subject = @msg;
                        END
            ELSE IF @State = 6 AND @State_Desc = 'OFFLINE'
                        BEGIN
                                    EXEC msdb.dbo.sp_send_dbmail
                                                  @recipients = @receipient_Address,
                                                  @profile_name = @profile,
                                                  @body = @msg,
                                                  @subject = @msg;
                        END
           
FETCH NEXT FROM db_cur INTO @DBName, @State, @State_Desc
END
CLOSE db_cur
DEALLOCATE db_cur


If we have to monitor multiple SQL Server instances then we create a SQL Table to store the name of the all SQL Server instances. Above mentioned SQL script can be used through OSQL Statement inside the batch script and loop through each SQL instances.