--- Useful Scripts for Log Shipping Monitor
select lmp.primary_server,lp.primary_database,lps.secondary_server,
lps.secondary_database,lp.backup_directory,
lp.backup_share,lp.monitor_server,lp.last_backup_date,
datediff(mi,lp.last_backup_date,getdate()) time_since_last_backup_min,
lp.last_backup_file,lp.backup_retention_period backup_retention_period_min,
lmp.backup_threshold backup_threshold_min,lmp.threshold_alert threshold_alert_min,
lmp.history_retention_period history_retention_period_min
from
msdb.dbo.log_shipping_primary_databases lp
join
msdb.dbo.log_shipping_monitor_primary lmp
on
lp.primary_id=lmp.primary_id
join
msdb..log_shipping_primary_secondaries lps
on
lmp.primary_id=lps.primary_id
select top 10 database_name,log_time,message,session_status
from msdb.dbo.log_shipping_monitor_history_detail
order by log_time desc
/*
Useful Scripts in Secondary Database Server
*/
select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server
--,lsd.block_size,lsd.buffer_count,lsd.max_transfer_size
from
msdb.dbo.log_shipping_secondary ls
join
msdb.dbo.log_shipping_secondary_databases lsd
on lsd.secondary_id=ls.secondary_id
join
msdb.dbo.log_shipping_monitor_secondary lms
on lms.secondary_id=lsd.secondary_id
---Check Error Log of Log SHipping Databases
select led.database_name,led.log_time,led.message,led.sequence_number,
led.session_id,led.source
from msdb.dbo.log_shipping_monitor_error_detail led
select top 10 database_name,log_time,message,session_status
from msdb.dbo.log_shipping_monitor_history_detail
order by log_time desc
