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