Tag: Troubleshooting DB Mirroring

  • 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

    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