Find databases involved in Logshipping
I have prepared a couple of queries which are useful in troubleshooting of Logshipping in SQL Server environment.
Query to run in Primary database involved in Logshipping:
select lsmp.primary_server,lsmp.Primary_database,lsps.secondary_server,lsps.secondary_database,
lsmp.backup_threshold [Backup Threshold in Mins],lspd.backup_directory,
lspd.backup_share,lsmp.last_backup_file [Latest file backed up],lspd.backup_retention_period [backup retention period],lspd.monitor_server,
lsmp.last_backup_date [Last Backup Date],lsmp.history_retention_period [History Retention Period in Mins]
from
msdb.dbo.log_shipping_primary_secondaries lsps join
msdb.dbo.log_shipping_monitor_primary lsmp on
lsps.primary_id = lsmp.primary_id
Join
msdb.dbo.log_shipping_primary_databases lspd
on
lsps.primary_id = lspd.primary_id
Above Query will give details about the Primary server, Secondary server, backup threshold, retention period and many more.
Query to run in Secondary database involved in Logshipping:
select lss.primary_server,lsms.secondary_server,lss.primary_database,lsms.secondary_database,
lss.backup_source_directory,lss.backup_destination_directory,
lss.file_retention_period [backup file retention period on disk in mins],lss.last_copied_file,lss.last_copied_date,
lssd.restore_delay [Delay time set for resore (Mins)],lssd.disconnect_users [Dissconnect users while restore],
lsms.restore_threshold [Restore threshold in Mins],
lsms.last_copied_file,lsms.last_copied_date,lsms.last_restored_file,lsms.last_restored_date,DATEDIFF(MINUTE,lsms.last_restored_date,getdate()) [Restoration Not happened from (Mins)]
from
msdb.dbo.log_shipping_secondary lss join
msdb.dbo.log_shipping_secondary_databases lssd
on lss.secondary_id = lssd.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lsms
on lss.secondary_id = lsms.secondary_id
Above Query will give the details from Secondary database perspective.
One query to run in secondary database is :
select lse.agent_type,lse.database_name,lse.log_time,message,primary_server,
restore_threshold,last_copied_file,last_copied_date,last_restored_date,last_restored_file
from
msdb..log_shipping_monitor_error_detail lse join
msdb..log_shipping_monitor_secondary lss
on lse.agent_id=lss.secondary_id
and log_time>=(dateadd(hh,-4,GETDATE()))
order by 3 desc
Above all queries will give you ample information to proceed further with the troubleshooting of Logshipping In SQL Server
Hope the queries helps
Thanks
