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.
To return the log shipping status using T-SQL execute the following code in a query window in SSMS:
The result is as follows:
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 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 for 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.
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 lot less information. It only shows the instance, database, instance type and the status. In most cases that’s all you need.
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 on 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“.