How to Monitor SQL Server Log Shipping?

Leave a comment (4) Go to comments

LOG Shipping can be monitored

  1. SQL Server Management Studio or via
  2. TSQL Queries

Prior to Monitor, let’s take a quick look a Log Shipping Scenario which we wanted to monitor

Primary Server Details

SQL Server Name


.\SQL1

Database Name

TEST_LOG_Shipping

Secondary Server Details, where we are shipping Logs

SQL Server Name

.\SQL2

Database Name

TEST_LOG_Shipping

**** Both SQL1 and SQL2 are named instances on my test Server

The Same can be verified graphically using SSMS by going

SSMS >>> Databases >>> USER Database, which was set up for LOG Shipping >>> Right Click >>> Properties >>> Click Transaction LOG Shipping and you will get to know the same details, as shown below

How to Monitor SQL Server Log Shipping? whether LOG SHIPPING is up to date or not sql server LOG Shipping Monitoring log shipping SQL Server monitor logshipping Monitor / Check Log Shipping information how to get information about working current log shipping set up

Let’s Monitor /  Check, whether LOG SHIPPING is up to date or not ?

Monitor LOG Shipping status Using SSMS

SSMS >>> Server Instance >>> Right Click >>> Reports >>> Standard Reports >>> Transaction LOG Shipping Status

How to Monitor SQL Server Log Shipping? whether LOG SHIPPING is up to date or not sql server LOG Shipping Monitoring log shipping SQL Server monitor logshipping Monitor / Check Log Shipping information how to get information about working current log shipping set up

Once you Select / Click this Option, you will get the Detailed report

How to Monitor SQL Server Log Shipping? whether LOG SHIPPING is up to date or not sql server LOG Shipping Monitoring log shipping SQL Server monitor logshipping Monitor / Check Log Shipping information how to get information about working current log shipping set up

This Report reveals the following information

  • Current Status of Primary and Secondary Databases
  • List of Databases, which are currently enable for log shipping and their corresponding Secondary Databases.
  • List of Databases, which are currently enabled as secondary in log shipping
  • BACKUP - Primary Databases
    • Minutes to last Transaction Log backup
    • Threshold value for Alert in case there is no backup for specified time
    • Whether the Alert for backup job is enabled or not
  • Copy
    • Time Since Last copy was performed
  • RESTORE
    • Time Since Last Restore
    • Latency of Last Files
    • Threshold value for Alert in case there is no restore for specified time
    • Whether the Alert for restore job is enabled or not
  • Last Backup File
  • Last Restore File

For each role performed by the current server instance, you can view the following information:

Role Information displayed
Monitor The name and status of every primary server and secondary server that uses this server instance as its monitor server.
Primary For each primary database, the status and name of the current server instance (as the primary server), along with the primary database name. The report displays the status of the backup job (which is stored locally on the primary server).
 
The report also contains a row for each of the corresponding secondary servers. If the configuration uses a monitor server and the stored procedure can connect to the monitor, these rows display the copy status and restore status for the most recent log backup.
Secondary For each secondary database, the status and name of the current server instance (as the secondary server), along with the secondary database name.
 
The report displays the status of the copy and restore jobs at the secondary server.
 
The report also contains a row for the corresponding primary server. If the configuration uses a monitor server and the stored procedure can connect to the monitor, this row displays the status of the most recent log backup.

Monitor LOG Shipping status Using TSQL Statements

The following TSQL statement, will give you exact same information, which we received the graphical format.

-- Show Current Status of Log Shipping 
exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N''

OUTPUT

How to Monitor SQL Server Log Shipping? whether LOG SHIPPING is up to date or not sql server LOG Shipping Monitoring log shipping SQL Server monitor logshipping Monitor / Check Log Shipping information how to get information about working current log shipping set up

We can easily check the TSQL Code for any Graphical operation in SSMS using Profiler, here is code, which I checked for Database Transaction LOG Shipping.

How to Monitor SQL Server Log Shipping? whether LOG SHIPPING is up to date or not sql server LOG Shipping Monitoring log shipping SQL Server monitor logshipping Monitor / Check Log Shipping information how to get information about working current log shipping set up

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from "DBATAG ", so that you do not miss out anything on SQL Server !!!

EOF - How to Monitor SQL Server Log Shipping?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

4 Comments.

  1. Can you setup SQL 2008 R2 Express as a monitor server for log shipping?

    • FROM SQL BOL

      Since SQL Server 2005 Service Pack 1 (SP1), database mirroring partners and witnesses have been supported by SQL Server Standard and Enterprise. But the partners must use the same edition, and asynchronous database mirroring (high-performance mode) is supported only by SQL Server Enterprise. Witnesses are also supported by SQL Server Workgroup and Express.

    • Nope, Log shipping is supported in the SQL Server Enterprise, Standard, and Workgroup editions.

      • I know that Express with NOT support Log Shipping, what I was asking is if you can use Express as a monitor server for Log Shipping.

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.