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
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
