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.

Get Log Shipping Errors 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.

Get Log Shipping Errors No Filter

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!

Get Log Shipping Errors No Filter Count

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.

Get Log Shipping Errors Filter Date From

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:

Get Log Shipping Errors Filter Database

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.