Home » How to check Log shipping status in SQL Server 2008

How to check Log shipping status in SQL Server 2008

sql server DBA 999

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:

How to check Log shipping status in SQL Server 2008

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.

2 thoughts on “How to check Log shipping status in SQL Server 2008”

  1. You can get this to your email account using Database Mail feature in SQL Server. To achieve this you will need to setup Database Mail First. This will include setup a Profile and account which is used by Database mail.

    Use the Sp_send_dbmail with @query parameter to send results as needed. Database Mail is to be configured first which is explained in below article

    http://sqlserver-dba.co.uk/database-maintenance/sql-server-database-db-mail-configuration-step-by-step.html

    Query for this report is:

    exec sp_executesql @stmt=N’exec sp_help_log_shipping_monitor’,@params=N”

    if needed put this results into Temporary table or variable first and format it as need.

    Try the above and let me know if any issues..

Leave a Reply

Your email address will not be published. Required fields are marked *