SQL Server Management Studio Standard Reports – Transaction Log Shipping Status

We’re in the final server-level reports in this series of the Standard Reports that ship with SQL Server Management Studio (SSMS). Don’t worry, there are plenty more reports coming in the database and other objects; but this one is the last of the reports on the server.

I want to re-iterate a couple of things about the reports in SSMS. I’m covering the reports that you get out-of-the-box with SQL Server 2005, but starting with Service Pack 2 you can add your own custom reports. This is a far more powerful feature than you might first expect. It’s worth your time to learn a little about creating reports for Reporting Services and how to use these reports in SSMS – you can set up a very useful monitoring environment from within SSMS for your systems.

I also wanted to answer a couple of questions about the reporting subsystem in SSMS. These reports show up in any system capable of running the subsystem – which includes SQL Server Express Edition (the reports are even available in the Express edition of Management Studio) but they don’t run against SQL Server version 2000 or earlier.

With that all cleared up, let’s take a look at this report. As you can guess from the title, it shows you the status of Log Shipping – but in case you’re not familiar with that feature, let’s chat about it for a moment. Log Shipping is the process of taking a Transaction Log (which stores all of the transactions that have happened on a server) from a “Primary” database, backing it up, copying it to a share on another computer, and restoring it to a copy of the database on that “Secondary” server. Using this process, you can effectively keep two systems “loosely coupled”, or in time-delayed sync. There are some restrictions around which editions support this feature, and which settings the database needs, and you can read more about those here.

As you can imagine, it’s important to keep track of the status of the entire process, since it involves everything from the database to the file system to the network. The report is pretty easy to understand and follow. You’ll get rows of data, one for the Primary database and one for the Secondary, and if you have it set up, for the monitor as well:

Column	Description
Status	Collective status of agents for the log shipping database: 0 = healthy and no-agent failures. 1 = There’s a problem
Primary Database -- Secondary Database	Indicates whether this row is for the primary or secondary database
Backup - Time Since Last	The length of time, in minutes, since the last log backup.
Backup - Threshold	The length of time, in minutes, after the last backup before a threshold_alert error is raised. backup_threshold is int, with a default of 60 minutes.
Backup - Alert Enabled	Specifies whether an alert will be raised when backup_threshold is exceeded. The value of one (1), the default, means that the alert will be raised.
Copy – Time Since Last	The length of time, in minutes, since the last log backup was copied.
Restore – Time Since Last	The length of time, in minutes, since the last log backup was restored.
Restore – Latency of Last File	Duration of time, in minutes, from the creation of the last backup to restore of the backup.
Restore – Threshold	The number of minutes allowed to elapse between restore operations before an alert is generated.
Restore – Alert Enabled	Specifies whether an alert will be raised when backup_threshold is exceeded. The value of one (1), the default, means that the alert will be raised.
Last Backup File	The name of the last successful log backup file.
Last Copied File	The name of the last successfully copied log backup file.
Last Restored File	The name of the last successfully restored log backup file.