---Below Script is used to find the time of oldest transaction log which was last sent to Mirroring DB (Time lag between Principal and Mirror DB)
--Below Script will append the Database mirroring to monitor table
--if the same is not updated in last 15 seconds (approx)
create table #dbmmonitor
(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',0,1
declare @time_recorded datetime, @time_behind datetime
select @time_recorded=time_recorded,@time_behind=time_behind from #dbmmonitor
declare @hour int,@min int,@sec int,@day int
declare @old_unsent_trn varchar(100)
select @hour=(DATEDIFF(hh,@time_behind,@time_recorded))%24
select @min=(DATEDIFF(mi,@time_behind,@time_recorded))%60
select @sec=(DATEDIFF(ss,@time_behind,@time_recorded))%60
select @day=(DATEDIFF(ss,@time_behind,@time_recorded))/86400
select @old_unsent_trn=convert(varchar(20),@day)+':'+convert(varchar(20),@hour)+':'+convert(varchar(20),@min)+':'+convert(varchar(20),@sec)
select @old_unsent_trn 'Oldest Unsent Transaction'
drop table #dbmmonitor
GO
---Using msdb.dbo.dbm_monitor_data table for results
exec msdb.dbo.sp_dbmmonitorresults 'dbmir',0,1
---
declare @local_time1 datetime,@end_of_log_lsn1 bigint,@failover_lsn1 bigint
declare @local_time2 datetime,@end_of_log_lsn2 bigint,@failover_lsn2 bigint
declare @min int,@hour int,@day int,@min1 int
declare @old_unsent_trn varchar(100)
select top(1) @local_time1=local_time,@end_of_log_lsn1=end_of_log_lsn,
@failover_lsn1=failover_lsn
from msdb.dbo.dbm_monitor_data
order by local_time desc
--select @local_time1 local_time1,@end_of_log_lsn1 end_of_log_lsn1
--,@failover_lsn1 failover_lsn1
select top(1) @local_time2=local_time,@end_of_log_lsn2=end_of_log_lsn,
@failover_lsn2=failover_lsn
from msdb.dbo.dbm_monitor_data
where end_of_log_lsn=@failover_lsn1
order by local_time desc
--select @local_time2 local_time2,@end_of_log_lsn2 end_of_log_lsn2
--,@failover_lsn2 failover_lsn2
select @day=(DATEDIFF(ss,@local_time2,@local_time1))/86400
select @min1=DATEDIFF(mi,@local_time2,@local_time1)
select @hour=convert(int,@min1)/60
select @min=convert(int,@min1)%60
--select @day,@hour,@min1
select @old_unsent_trn=convert(varchar(20),@day)+':'+convert(varchar(20),@hour)+':'+convert(varchar(20),@min)
select @old_unsent_trn 'Oldest Unsent Transaction'
