By: Mohammed Moinudheen | Updated: 2019-09-06 | Comments (1) | Related: > Log Shipping
Problem
We have a requirement to failover the primary SQL Server log shipping database to the secondary server and setup log shipping in the reverse direction. After the change, the SQL Server log shipping status report is showing failures. I see that the old copy and restore jobs still exist on the server as I had not removed them cleanly before performing the database failover. Is there a way to fix the alerts on the log shipping status report?
Solution
This tip assumes you already have a log shipping environment, but configuring log shipping is a straight forward process and you can refer this tip for detailed step by step instructions if needed.
In this demo, we have log shipping already configured for a database named "mo" which we will use for the examples.
SQL Server Log Shipping Database Status
From the screenshots, you can see the primary and secondary SQL Server instances and that database "mo" is configured for log shipping. For your reference, in this demo, the primary SQL instance is: BOULCOTT\A1 and the secondary SQL instance is BOULCOTT\A2.
In SQL Server Management Studio, you can check the status of log shipping by right clicking on the SQL instance and then click on "Reports" and then click on "Transaction Log Shipping Status".
Below is the screenshot of the log shipping status from the primary (BOULCOTT\A1) SQL instance.
Below is the screenshot of the log shipping status from the secondary (BOULCOTT\A2) SQL instance.
From the log shipping status screenshots on the primary (BOULCOTT\A1) and secondary (BOULCOTT\A2) SQL instances, you can see that the log shipping status is Good.
Perform Log Shipping Failover to Secondary Server
Follow the below steps to perform a manual failover of the database to the secondary (BOULCOTT\A2) server.
Create Tail Log Backup
As the primary database is online, perform a tail log backup on the primary (BOULCOTT\A1) server. Use this sample script to perform the operation, this step will also put the primary database into a restoring state.
BACKUP LOG mo TO DISK='C:\Temp\LogShip\mo_tail_1.trn' WITH NORECOVERY, STATS
Disable Log Shipping Jobs
Disable the log shipping jobs on the primary (BOULCOTT\A1) and secondary (BOULCOTT\A2) servers.
Once done, run the LSCopy and LSRestore jobs on the secondary (BOULCOTT\A2) server. This will bring the secondary database up to date.
After the copy and restore jobs are run successfully, recover the database on the secondary (BOULCOTT\A2) server. Once done, this database becomes the new primary database. For recovering the database, run the below script on the secondary (BOULCOTT\A2) server.
RESTORE DATABASE mo WITH RECOVERY
You will see this screen when you perform the recovery operation.
Reconfigure SQL Server Log Shipping on New Primary Server to New Secondary Server
Once you configure SQL Server log shipping from the new primary (BOULCOTT\A2) server to the new secondary (BOULCOTT\A1) server, you will see new log shipping jobs that get created on the servers. Run the new log shipping jobs to confirm that the log shipping configuration is working as expected. Below are the screenshots from the new log shipping configuration.
Check Log Shipping Status
As shown previously, check the status of log shipping by right clicking on the SQL instance and check the log shipping status in the reports tab. This will show you the status of log shipping. Usually, right after you perform the failover, the log shipping status will display as "Good" as it may have not crossed the threshold limits for both backup and restore. You can adjust the threshold settings on the original primary and original secondary server by running the below commands.
Run this on the original primary (BOULCOTT\A1) server. This setting adjusts the backup threshold to 10 minutes which means there will be a log shipping alert if the log backup job didn’t run in the last 10 minutes.
EXEC master.dbo.sp_change_log_shipping_primary_database @database = N'mo', @backup_threshold = 10
Run this on the original secondary (BOULCOTT\A2) server. This setting adjusts the restore threshold to 10 minutes which means there will be a log shipping alert if the restore job didn’t run in the last 10 minutes.
EXEC master.dbo.sp_change_log_shipping_secondary_database @secondary_database = N'mo', @restore_threshold = 10
Once you run these commands, check the status of log shipping as described previously to see any alerts. You will notice that the details of the old log shipping configuration are still stored in SQL Server and show the log shipping configuration to be out of sync. Below is the screenshot from the new primary (BOULCOTT\A2) server. From the screenshot, the details are related to the restore jobs on the original secondary (BOULCOTT\A2) server.
Below is the screenshot from the new secondary (BOULCOTT\A1) server. From the error message, you can see that the failure is related to the log shipping backup job on the original primary (BOULCOTT\A1) server.
As the alerts are misleading, we will see in the following section ways which we can remove these false alerts.
Log Shipping Alert Job Fails on Both Primary and Secondary Servers
From the log shipping status report, you will have noticed by now that the meta data related to the original log shipping configuration is persisted thereby raising a false alert. If you check the Log Shipping alert job on either the primary or secondary server, you will notice that the job is failing. This is a screenshot from the new primary (BOULCOTT\A2) server.
Fix Log Shipping Alerts by Removing Old Metadata
In order to fix these alerts, you will need to run a few commands on both the original primary (BOULCOTT\A1) and original secondary (BOULCOTT\A2) server. Run these commands on the original secondary (BOULCOTT\A2) server (the new primary).
First Fix Alerts on Original Secondary Server (new primary)
Run the below command on the master database of the original secondary (BOULCOTT\A2) server (your new primary server).
sp_delete_log_shipping_secondary_database @secondary_database = 'mo' --update correct DB name
Once done, this will delete the copy and restore jobs from the original secondary (BOULCOTT\A2) server. From the screenshot, you can see that the old copy and restore jobs are deleted from the server.
The next step is to run the below command on the master database of the original secondary (BOULCOTT\A2) server (new primary server).
sp_delete_log_shipping_secondary_primary @primary_server = 'BOULCOTT\A1', @primary_database = 'mo' --update correct DB name
This will remove history about the original primary (BOULCOTT\A1) server. Once done, you can check the log shipping status report on the original secondary (BOULCOTT\A2) server and you will notice that the alerts have cleared.
First Fix Alerts on Original Primary Server (new secondary server)
Run the below command on the master database of original primary (BOULCOTT\A1) server (new secondary server).
sp_delete_log_shipping_primary_secondary @primary_database = 'mo', -- update correct DB name @secondary_server = 'BOULCOTT\A2', -- update correct server name @secondary_database = 'mo' -- update correct DB name
This will remove history related to the original secondary (BOULCOTT\A2) server that was still stored thereby causing the false alerts. Once done, run the below command which will remove the original transaction log backup job on the server.
sp_delete_log_shipping_primary_database @database = 'mo' -- update correct DB name
You will see that the log shipping backup job that was present before gets deleted from the original primary (BOULCOTT\A1) server.
Now, when you check the log shipping status report on the original primary (BOULCOTT\A1) server, you will notice that the false alerts have cleared and you will see the log shipping status information for the current configuration.
Check Status of Log Shipping Alert Job on New Primary and New Secondary Servers
After you run above steps, you can check the status of the Log Shipping alert job that run on both the primary (BOULCOTT\A2) and secondary (BOULCOTT\A2) servers. Below is a screenshot from the new secondary (BOULCOTT\A1) server (original primary server).
You can see that the alerts have cleared and the job is running successfully. You can confirm the same on the other server too.
Clean Way to Remove Log Shipping Configuration
If due to some requirement, you are looking to remove the log shipping configuration, a clean way to remove the configuration is by right clicking on the primary database, go to "Properties", click on the "Transaction Log Shipping" tab on the left and then unchecking the option "Enable this is a primary database in a log shipping configuration". This will remove all the log shipping jobs – backup, copy and restore cleanly from both the primary and secondary servers and will also not cause any unnecessary log shipping alerts.
In the pop-up window that is displayed, click on "Yes" and select "OK". This will automatically delete all the log shipping jobs and history related to the log shipping configuration. You will see a confirmation window that the log shipping configuration is successfully deleted.
Next Steps
- In this tip, you learned about performing a failover of a log shipping configuration
- In this tip, you learned about log shipping status alerts that originate from an old log shipping configuration
- In the demo, you saw details of the alerts from both the primary and secondary servers
- In the demo, you learned how to adjust the threshold limits of the log shipping configuration
- In the demo, you learned how to fix the alerts on both the primary and secondary databases and servers
- You also learned a clean way of removing the log shipping configuration that deletes all the log shipping jobs and history related to the log shipping configuration
- Try out this demo on your own test environment
- Refer to these other articles related to log shipping
About the author
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-09-06
