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.

[sourcecode language=”sql”]
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring

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

Reference: Ritesh Shah

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

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

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

Comments are closed.