Useful queries while troubleshooting Database Mirroring

Useful queries while troubleshooting Database Mirroring

Query to check the associated ports with DB Mirroring
[sourcecode language="sql"]
SELECT type_desc, port FROM sys.tcp_endpoints;
GO
[/sourcecode]

Query to check the state of the DB Mirroring
[sourcecode language="sql"]
SELECT state_desc FROM sys.database_mirroring_endpoints
GO
[/sourcecode]

Query to check the service account connect permission on the DB Mirror endpoints
[sourcecode language="sql"]
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
[/sourcecode]

Query to check the DB Mirror timeout and resetting the DB Mirror timeout
[sourcecode language="sql"]
SELECT mirroring_connection_timeout
FROM
sys.database_mirroring
GO

ALTER DATABASE SQLDBPOOL SET PARTNER TIMEOUT 15
GO
[/sourcecode]