Log Shipping With dbatools - Part 2: Test Log Shipping Status

In the first part of the series I described the command Invoke-DbaLogShipping.

This makes it possible to set up log shipping. This blog post will be about the command to test log shipping status. Before I describe the command, I want to discuss the options that are available in the SQL Server Management Studio (SSMS) and SQL Server.

Out-of-the-box monitoring⌗

Microsoft has made it possible to check the log shipping using SSMS. This can be done using queries or by using the “Transaction Log Shipping Status” report.

Queries⌗

To return the log shipping status using T-SQL execute the following code in a query window in SSMS:

EXEC master.dbo.sp_help_log_shipping_monitor

The result is as follows:

This a good method to find any possible issues but you need to check multiple columns to find out if something is wrong.

Transaction Log Shipping Status Report⌗

By default, SSMS has a report that shows the status of the log shipping. To open the report right-click on the server, go the “Reports”, Go the “Standard Reports” and click on “Transaction Log Shipping Status”.

The result is an overview of each database with the status of that process:

The report shows in the red colored text if something is wrong which is a great way to find any problems. Still, we need to go into SSMS and click around before we get to this information.

Why this command⌗

Monitoring your log shipping processes is important. You need the synchronization status of the log shipped databases. The log ship process consists of three steps; Backup, Copy and Restore. The log shipping tracks the status of these processes.

It registers the last transaction log backup, the last file copied and the last file restored. It also keeps track of the time since the last backup, copy and restore.

But that’s not all. Log shipping also checks if the threshold for the backup and restore has been exceeded.

During the log shipping thresholds are set for the backup and restore process. The default thresholds are 60 minutes for the backup and 45 minutes for the restore. There is no threshold for the copy process. If for example, the last time since the last backup exceeds the backup threshold an alert will be triggered.

That’s a lot of information to consider and that’s why this command was developed. It will enable the user to get a complete overview of the log shipping status without having to know all the details.

Test-DbaLogShippingStatus⌗

The commands will return a lot of the information by default. It will collect all the information and based on that returns a status. To execute the command:

Test-DbaLogShippingStatus -SqlInstance sql1

The result is a detailed overview of each database including the status.

This looks a lot like the results you get from the queries of the report we talked about earlier. All this information can be a little overwhelming and it’s not always needed. If you don’t need all that information there is an option to format and filter the output.

It will only show you the core information to know what the status of the log shipping is.

Test-DbaLogShippingStatus -SqlInstance sql1 -Simple

The result of the command:

As you can see there is a lot less information. It only shows the instance, database, instance type, and status. In most cases, that’s all you need.

More filtering⌗

The command also allows you to filter out the primary and the secondary databases.

Test-DbaLogShippingStatus -SqlInstance sql1 -Primary

Test-DbaLogShippingStatus -SqlInstance sql2 -Secondary

There is a way to filter specific databases using the “-Database” parameter.

Test-DbaLogShippingStatus -SqlInstance sql2 -Database DB1, DB2, DB2

Of course, there is also an option to exclude certain databases by using the -ExcludeDatabase parameter.

Test-DbaLogShippingStatus -SqlInstance sql2 -ExcludeDatabase DB4, DB5

This concludes the command Test-DbaLogShippingStatus.

The next post will be about the command Get-DbaLogShippingError.