Script to Monitor SQL Server Database Mirroring Status

SQL Server Database Mirroring is fantastic HA solution developed by Microsoft. Microsoft lunch database mirroring with SQL Server 2005. Database Mirroring send logs to mirror from principle. We need to monitor Database Mirroring status continuously to ensure that everything is working fine. We can use MSDB.SYS.SP_DBMMONITORRESULTS system store procedure to monitor database mirroring.

Below script will convert number to actual values during monitoring. You can use below script to monitor the database mirroring status.

CREATE TABLE #DBMRESULTS

(

DATABASE_NAME VARCHAR(255),

ROLE INT,

MIRRORING_STATE TINYINT,

WITNESS_STATUS TINYINT,

LOG_GENERAT_RATE INT,

UNSENT_LOG INT,

SENT_RATE INT,

UNRESTORED_LOG INT,

RECOVERY_RATE INT,

TRANSACTION_DELAY INT,

TRANSACTION_PER_SEC INT,

AVERAGE_DELAY INT,

TIME_RECORDED DATETIME,

TIME_BEHIND DATETIME,

LOCAL_TIME DATETIME

)

INSERT INTO #DBMRESULTS

EXEC MSDB.SYS.SP_DBMMONITORRESULTS ‘USERDB1’, 0,1

SELECT DATABASE_NAME,

(CASE ROLE

WHEN 1 THEN ‘PRINCIPAL’

WHEN 2 THEN ‘MIRROR’

END) ROLE_OFDB_QUERY_FIRED,

(CASE MIRRORING_STATE

WHEN 0 THEN ‘SUSPENDED’

WHEN 1 THEN ‘DISCONNECTED’

WHEN 2 THEN ‘SYNCHRONIZING’

WHEN 3 THEN ‘PENDING FAILOVER’

WHEN 4 THEN ‘SYNCHRONIZED’

END) MIRRORING_STATE,

(CASE WITNESS_STATUS

WHEN 0 THEN ‘UNKNOWN’

WHEN 1 THEN ‘CONNECTED’

WHEN 2 THEN ‘DISCONNECTED’

END) WITNESS_STATUS,

LOG_GENERAT_RATE,

UNSENT_LOG,

SENT_RATE,

UNRESTORED_LOG,

RECOVERY_RATE,

TRANSACTION_DELAY,

TRANSACTION_PER_SEC,

AVERAGE_DELAY,

TIME_RECORDED,

TIME_BEHIND,

LOCAL_TIME

FROM #DBMRESULTS

DROP TABLE #DBMRESULTS

http://gallery.technet.microsoft.com/Script-to-Monitor-SQL-3d25e132

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx