Monitoring Transaction Log Shipping in SQL Server

Transaction Log shipping is a great and easy way to replicate a multitude of Microsoft SQL server databases. This article will discuss how we can monitor the status of the replication. This is useful to add to the daily health checks or maybe to check the replication after one server is upgraded and one wishes to resume replication.

When Transaction Log Shipping is setup, 3 jobs are created by SQL server on the secondary database server:

1. LSCopy - This job copies the transactions logs from the primary to the secondary server
2. LSRestore - This will restore the transaction log which was copied by the previous job
3. LSAlert - This job will send any alerts if there are any errors

By looking at the Job History of the LSRestore job, we can see all the transaction logs which are being restored:

This screen will also show the path of the transaction logs - one can therefore compare the timestamps and check if there is any lagging transaction logs.