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.
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.
View Comments
Thanks ! But how can get this report through e-mail ?
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..