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.
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.
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 my all my tools together and make it easy to solve any errors.
The other way to see if there are any errors in the log shipping is by using the 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.
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 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)
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:
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”.