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

Standard

Reading Time: 3 minutes

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

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

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

Personally I don’t like using the event viewer because there could be other errors which I would have to filter through before getting to the root cause. The other reason this doesn’t give me a complete view is because 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 tool set 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:

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 the last hour.

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.

More filtering

Of course there are other filters to make it easier to zoom in into a specific problem.

It’s possible to filter on the databases:

Get Log Shipping Errors Filter Database

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

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

 

This concludes the command “Get-DbaLogShippingError”. The next post will be about the command “Invoke-DbaLogShippingRestore”.

Leave a Reply