Log Shipping With dbatools – Part 3: Get Log Shipping Errors

In the second part of the series we discussed the way to test the log shipping status using the command “Test-DbaLogShippingStatus”.

This blog will be about how to get log shipping errors to analyze why your log shipping isn’t working.

Out-of-the-box solutions⌗

There are several options to get the log shipping errors right out of the box. One of them is using queries and the other one is the event viewer.

Queries⌗

Some queries can help to get log shipping errors. One that is likely used is by using the log_shipping_monitor_error_detail table.

SELECT database_name,
       sequence_number,
       log_time,
       log_time_utc,
       message,
       source,
       help_url
FROM [msdb].[dbo].[log_shipping_monitor_error_detail]

Although this is a good way to get the log shipping errors, unless I’m using CMS or a monitoring server, I have no way to find errors on multiple instances with log shipped databases.

Furthermore, I want to keep all my tools together and make it easy to solve any errors.

Event Viewer⌗

The other way to see if there are any errors in the log shipping is by using the event viewer.

I don’t like using the event viewer because there could be other errors that I would have to filter through before getting to the root cause. The other reason this doesn’t give me a complete view is that it doesn’t register any errors that occurred with restore of the backups.

Why this command⌗

When there is something wrong with the log shipping I want to know about it as soon as possible. Using the above options doesn’t give me the flexibility to do that.

For instance, I cannot check multiple instances at once, unless I’m using CMS or a monitoring server. To make the toolset complete this command was needed to make sure a user would be able to get an overview of the log shipping status.

Get-DbaLogShippingError⌗

By default, the command will return all the errors that ever occurred. It collects all the information and returns it in a table structure to the user. To execute the command:

Get-DbaLogShippingError -SqlInstance sql1

The result is an overview of the errors.

In my play area, I made sure I would have a lot of errors to test the command. It turned out I had more than 3100 errors!

This brings us to one of the reasons I created this command; filtering the results. I may only want to see the errors that occurred in the last hour.

Get-DbaLogShippingError -SqlInstance sql1 -DateTimeFrom (Get-Date).AddHours(-1) | Format-Table

The result is still a quite big list but more manageable.

In this example we’re only using the parameter -DateTimeFrom but we can also use -DateTimeTo to filter between certain periods.

Get-DbaLogShippingError -SqlInstance sql1 -DateTimeFrom (Get-Date).AddHours(-10) -DateTimeTo (Get-Date).AddHours(-5)

More filtering⌗

Of course, there are other filters to make it easier to zoom in on a specific problem. It’s possible to filter the databases:

To filter on the type of instance you can use the “-Primary” and “-Secondary” parameters

Get-DbaLogShippingError -SqlInstance sql1 -Database AdventureWorks2014_LS -Primary

Get-DbaLogShippingError -SqlInstance sql1 -Database AdventureWorks2014_LS -Secondary

To filter on the specific actions use the “-Action” parameter:

Get-DbaLogShippingError -SqlInstance sql1 -Database AdventureWorks2014_LS -Action Copy

This concludes the command Get-DbaLogShippingError.

The next post will be about the command Invoke-DbaLogShippingRestore.