Home » SQL Server » Script to Monitor SQL Server Database Mirroring Status

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

Advertisements

2 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: