Friday 27 July 2018

Database Mirroring : TSQL Health Scripts

-- Dynamic Managment View of configuration information

SELECT db_name(sd.[database_id])           AS [Database Name]
      ,sd.mirroring_state                  AS [Mirror State]
      ,sd.mirroring_state_desc             AS [Mirror State]
      ,sd.mirroring_partner_name           AS [Partner Name]
      ,sd.mirroring_role_desc              AS [Mirror Role] 
      ,sd.mirroring_safety_level_desc      AS [Safety Level]
      ,sd.mirroring_witness_name           AS [Witness]
      ,sd.mirroring_connection_timeout AS [Timeout(sec)]
FROM sys.database_mirroring AS sd
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];

-- history of all mirored transactions in last 2 hours

USE msdb;

EXEC sp_dbmmonitorresults DatabaseName, 2, 0;



--os performance counters about mirroring

SELECT *,[counter_name] as CounterName,[cntr_value] as CounterValue
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [instance_name]='mirroredDatabase'

No comments: