mssqltips logo

Cleanup SQL Server Log Shipping Alerts After Failover

By:   |   Updated: 2019-09-06   |   Comments   |   Related: More > 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.

ssms object explorer

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.

log shipping status report

Below is the screenshot of the log shipping status from the secondary (BOULCOTT\A2) SQL instance.

log shipping status report

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.

sql agent jobs
sql agent jobs

Once done, run the LSCopy and LSRestore jobs on the secondary (BOULCOTT\A2) server. This will bring the secondary database up to date.

job window
job window

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.

restore database

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.

sql agent jobs for log shipping
sql agent jobs for log shipping

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.

log shipping status report

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.

log shipping status report

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.

sql agent job history
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.

sql agent jobs for log shipping

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.

log shipping status report

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.

sql agent jobs for log shipping

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.

log shipping status report

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).

job history

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.

log shipping configuration

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.

log shipping configuration
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


Last Updated: 2019-09-06


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools