SQL Server Training | Tutorials | How to's

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

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

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

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

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.

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 !!!