Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Different ways to monitor Log Shipping for SQL Server databases

MSSQLTips author Jugal Shah By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Log Shipping
Problem

Log shipping has been an option for creating a failover server for SQL Server for quite some time.  In this tip, we look at different ways that you can monitor the status of your log shipped databases.

Solution

Log Shipping is a basic SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover or reporting purposes.

You can use the below items to investigate if there are any issues with your databases that are setup for Log Shipping.  We will cover each of these items and how they can be used.

  • SQL Server Error Log
  • SSMS Built In Report
  • System Stored Procedures
  • Query the MSDB database
  • Application/System EventViewer Log

SQL Server Error Log

Check the SQL Server error log for error messages related to log shipping such as database backup and restore failures using these commands.

-Query to check the Log Shipping related error messages
select * from sys.sysmessages where description like '%shipping%' and msglangid = 1033
--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Error",Null
--Execute it on Primary/Secondary server
EXEC xp_readerrorlog 0,1,"Shipping",Null
--Execute it on Primary server
EXEC xp_readerrorlog 0,1,"Backup",Null
--Execute it on secondary server
EXEC xp_readerrorlog 0,1,"Restore",Null

SSMS Built In Report

You can use the Log Shipping Status report by right clicking on the Server Name in Management Studio > Reports > Standard Reports > Transaction Log Shipping Status.

sql server standard reports options

Once you click on the Log Shipping Status report, you will get a report as shown below. You can open the status report for the monitoring server, primary server or secondary server. The report will show you the log shipping status (whether it is healthy or not) as well as metadata such as the primary and secondary database names, time since the last backup, last restore file, etc...

sql server transaction log shipping status report

System Stored Procedures

You can execute the below Log Shipping System Stored Procedure to monitor log shipping and get detailed information about log shipping.

  • sp_help_log_shipping_monitor
    • This is the how SQL Server generates the Log Shipping Status report by executing sys.sp_help_log_shipping_monitor procedure. This procedure returns the log shipping status (whether it is healthy or not) as well as metadata such as primary and secondary database names, time since last backup, last backup file, last restore file, etc...
  • sp_help_log_shipping_monitor_primary
    • returns all columns from the log_shipping_monitor_primary table for the specified primary log shipping database. It returns server name, database name, time of last backup, backup threshold, threshold alert and history retention period.
  • sp_help_log_shipping_monitor_secondary
    • returns all columns from log_shipping_monitor_secondary table for the specified secondary log shipping database. It will return database name, server name, restore threshold, last copied file, time of last copy / restore and history retention period.

Query the MSDB database

You can monitor the log-shipping jobs and errors from the MSDB tables as well.

--Query to list out the Log Shipping Jobs
SELECT * 
FROM msdb.dbo.sysjobs 
WHERE category_id = 6
--Query to check the job history error messages if any
SELECT * 
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like '%Operating system error%'
--Query to check the Log Shipping errors
SELECT * 
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]
WHERE [message] like '%Operating system error%'

Application/System Event Viewer Log

Another option is to check the Application/System Event Viewer Log for any log shipping, backup, restore or system related issues.

Event Viewer look for log shipping related errors
Next Steps
  • Document your the log shipping configuration information for all servers that are setup for log shipping.
  • Create a Job to run the log shipping status report stored procedure and send an email with the details.


Last Update: 12/21/2011


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, December 21, 2011 - 3:55:41 PM - Kevin parks Read The Tip

Redgate software has a free Log Shipping monitor tool that is very handy as well. 

 

http://labs.red-gate.com/Tools/Details/LSM

 

enjoy....

 

kevin

 

 


Thursday, December 06, 2012 - 6:53:46 AM - Saurabh sharma Read The Tip

 

Sir,

 

My job is to generate transaction log shipping report , edit the report(extract out secondary server details and leave primary server), and than mail this report to senior member of my team.

 

currently

 

I'm manually generating report

 

Editing the report using VBA excel

 

Mailing the report by Outlook

 

 

 

can this job be automated ?? I don't have much experience of windows, so can you please guide me how to accomplish this task as this is very ***bersome

 


Monday, April 29, 2013 - 6:43:45 AM - Dhanesh Read The Tip

Hi Jugal

 

can you pls tell me how to Create a Job to run the log shipping status report stored procedure and to get an email with the detail????


Thanks in advance


Friday, May 10, 2013 - 10:38:45 PM - DineshSpecial Read The Tip
You can check error log of log shipping status wheater it is working properly or not.I giving both scerio 
 
1) Not working status:-
Message
The log shipping secondary database DINESH\R2.AdventureWorksLT2008 has restore threshold of 45 minutes and is out of sync. No restore was performed for 1919 minutes. Restored latency is 1 minutes. Check agent log and logshipping monitor information.
 
2)Working Status:-
Message
Log was restored. Database: AdventureWorksLT2008, creation date(time): 2013/03/16(20:31:39), first LSN: 38:908:1, last LSN: 38:1065:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\dinesh\db\AdventureWorksLT2008_20130511023155.trn'}). This is an informational message. No user action is required.
 
 
Thanks,
Dinesh

Saturday, May 11, 2013 - 12:59:34 AM - Dhanesh Read The Tip

Hi all

 

I should get a mail stating the status og log shipping i.e good or bad status

or /last copied/restored file in mail ..how can i do that ???

 

plz reply


Tuesday, September 24, 2013 - 6:53:45 AM - Vandy Read The Tip

When an alert job fails, where does it store the information or message. DOes it directly makes an entry in msdb?

 

 

** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.