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
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.