How to check Log shipping status in SQL Server 2008:
SQL server recovery model has to be Full recovery mode for log shipping which allows log backups.
sql server database log backup is shiped and restore from one server or Primary to another server Secondary/destination server. Reports can be run from Secondary server for business needs. Using sql server nolock is recommended if dirty reads are allowed for reporting purposes.
This can be done by two methods:
1.By using the standard report on Instance level for Transaction log shipping status both on Primary and each secondary/destination servers individually.
On the Primary/source server, it shows when backup jobs succeeded.
On Secondary/Destination servers, it shows both when last copy job succeeded plus when the backups were restored.
This is shown below Screenshot:
2. Using the TSql below:
exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N''
This is the same as report above which needs to run on both Primary and each secondary servers to check the status.