---Script to find which Databases are involved in Mirroring
select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,
mirroring_safety_level_desc,mirroring_partner_name,mirroring_partner_instance,
mirroring_witness_state,mirroring_witness_state_desc
from master.sys.database_mirroring
where mirroring_state is not null
---Detailed Script to find which Databases are involved in Mirroring
select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,
mirroring_safety_level_desc,mirroring_safety_sequence
mirroring_partner_name,mirroring_partner_instance,
mirroring_witness_state,mirroring_witness_state_desc,
mirroring_failover_lsn,mirroring_connection_timeout,mirroring_redo_queue,
mirroring_end_of_log_lsn,mirroring_replication_lsn
from master.sys.database_mirroring
where mirroring_state is not null
---Below script is used to check the connection details
---which are involed in Database Mirroring
select state_desc,connect_time,login_time,authentication_method,principal_name,
remote_user_name,last_activity_time,is_accept,login_state_desc,
receives_posted,sends_posted,total_bytes_sent,total_bytes_received,
total_sends,total_receives
from sys.dm_db_mirroring_connections
---Checking EndPoint Status involved in Database Mirroring
select name,endpoint_id,protocol_desc,type_desc,state_desc,role_desc,
connection_auth_desc
from sys.database_mirroring_endpoints
---Run on Witness Server if the same exist to know the info abot Database
---involved in Mirroring
select database_name,principal_server_name,mirror_server_name,safety_level_desc,
partner_sync_state_desc,is_suspended,safety_sequence_number,role_sequence_number
from sys.database_mirroring_witnesses
---Below Stored Procedure returns status rows for a monitored database from the status table in
---which database mirroring monitoring history is stored (last 2 hours)
Exec msdb..sp_dbmmonitorresults 'dbmir',1,0
---Below Script returns status rows for a monitored database from the status table in
---which database mirroring monitoring history is stored (last 2 hours)
declare @dbmmonitor table
(database_name varchar(100),role varchar(100),
mirroring_state varchar(100),witness_status varchar(100),
log_generation_rate int,unsent_log int,send_rate int,
unrestored_log int,recovery_rate int,transaction_delay int,transactions_per_sec int,
average_delay int,time_recorded datetime,time_behind datetime,local_time datetime)
insert into @dbmmonitor
Exec msdb..sp_dbmmonitorresults 'dbmir',1,0
select database_name,
case role when '1' then 'Principal' when '2' then 'Mirror' else role end role,
case mirroring_state when '0' then 'Suspended' when '1' then 'Disconnected'
when '2' then 'Synchronizing' when '3' then 'Pending Failover'
when '4' then 'Synchronized' else role end mirroring_state,
witness_status,log_generation_rate,unsent_log,send_rate,unrestored_log,recovery_rate,
transaction_delay,transactions_per_sec,average_delay,time_recorded,time_behind,
local_time
from @dbmmonitor
order by local_time desc
