.::: Monitor Transaction Log shipping using T-SQL SQL Server :::.

 continue from http://teguhth.blogspot.com/2021/10/configure-log-shipping.html

A. Monitor Transaction Log shipping using T-SQL

1. Get last Backup of database with LSN (Publisher Side):

SELECT   d.name, b.*
FROM     master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.name AND b.type = 'L'
where d.name = clasicdb
--<dbnamenya apa>
ORDER BY backup_finish_date DESC
  

2. Get details of last restored transaction log Backup (Subscriber Side):

SELECT b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn, a.*
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
WHERE a.destination_database_name = clasicdb
--<dbnamenya apa>
ORDER BY restore_date DESC
 

B. Monitor Transaction Log shipping with SSMS
SQL Server instance -> Reports -> Standard Reports -> Transaction Log Shipping Status



https://www.sqlshack.com/monitor-transaction-log-shipping-using-t-sql-and-ssms/