Find mirroring status by querying sys.database_mirroring in SQL Server

Find mirroring status by querying sys.database_mirroring in SQL Server

I had already written articles about how to set mirroring and how to monitor mirroring with “Mirroring Monitor”. Today this is time to query “sys.database_mirroring” system table which contains record for each database available in SQL Server instance with NULL value in all column which starts with “mirroring_” if mirroring is not setup for the database.

sys.database_mirroring” gives your important information about principal/mirror database/server alongwith its status whether it is synchronized or not and what safety mode mirroring is configured on.

SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN 'Mirroring is On' ELSE 'No mirror configured' END AS IsMirrorOn,
mirroring_state_desc,
CASE WHEN mirroring_safety_level=1 THEN 'High Performance' WHEN mirroring_safety_level=2 THEN 'High Safety' ELSE NULL END AS MirrorSafety,
mirroring_role_desc,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring
GO

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

2 thoughts on “Find mirroring status by querying sys.database_mirroring in SQL Server

  1. Pingback: Mirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

Comments are closed.