Useful queries while troubleshooting Database Mirroring

Useful queries while troubleshooting Database Mirroring

Query to check the associated ports with DB Mirroring

   
SELECT type_desc, port FROM sys.tcp_endpoints;
GO

Query to check the state of the DB Mirroring
1
2
   
SELECT state_desc FROM sys.database_mirroring_endpoints
GO

Query to check the service account connect permission on the DB Mirror endpoints

   
SELECT 'Metadata Check';
SELECT EndPnt.name, SvrPerm.STATE,
   CONVERT(nvarchar(38), suser_name(SvrPerm.grantor_principal_id))
    AS GRANTOR,
   SvrPerm.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SvrPerm.grantee_principal_id))
    AS GRANTEE
   FROM sys.server_permissions SvrPerm, sys.endpoints EndPnt
   WHERE SvrPerm.major_id = EndPnt.endpoint_id
   ORDER BY Permission, grantor, grantee;
GO

Query to check the DB Mirror timeout and resetting the DB Mirror timeout

   
SELECT mirroring_connection_timeout
FROM
sys.database_mirroring
GO

ALTER DATABASE SQLDBPOOL SET PARTNER TIMEOUT 15
GO