Steps to Catch Up Log Shipping Secondary

 

If you are using Log Shipping, there have probably been times that you have discovered that the Log Shipping Secondary is not up to date.  Perhaps you found out by right clicking on your SQL Server instance and running the performance dashboard report for Log Shipping Status.  If you are not familiar with this report check here

Perhaps there is a Reporting team that uses that secondary and they met you at your desk upon arrival, informing you that the database is not online so they can’t work yet. So what do you do when you realize that the secondary is so far behind that it is probably not going to catch up on its own?  This process assumes that you have a Log Shipping config set up to run a Log Shipping Restore job just prior to the beginning of the business day and that job leaves the database in Standby mode so it can be read from.  At the end of the business day the log shipping restore job has one or more schedules set to  kick off restores for the transaction logs during the business day.

Here is a process you can use. 

1.       Disable the Copy Job on the secondary that is behind. 

2.       View the previously mentioned Log Shipping Status report and determine which file is the next in line to be restored. 

3.       Create a folder in the path where the transaction log backups are being copied to and perform a Cut operation on all the files just after the current file being restored. 

4.       Paste those files into the folder you made in step 3. 

Here is what you have accomplished with this.  You have set up the database to go to Read Only/Standby once the current file is done restoring.  It can’t continue the restore process because it has reached the last transaction log file in the original folder path. If the Reporting team can live with the data as is, then they can get to work while you set up the next part that will allow you to catch it up.  If it still is not usable for the Reporting team because the data has to be more current, then you have still helped yourself because you have to get the database out of recovery mode so you can do the next part to catch it up.

5.       Take a differential backup of the current database. 

6.       While that is running, open SQL Server Management Studio to set up your script to restore the most recent full backup and the differential you are making.  Remember to consider that you may have to use the WITH MOVE part of the RESTORE database syntax because the location of your files on the secondary may not be the same as on your primary.  Also, if your Log Shipping config is designed to leave the database in Standby then you will need to use the WITH STANDBY clause and then name the .tuf file where the redo information is kept.

7.       If the Reporting team started querying after step 3 you will need to ask them to disconnect because you can’t restore a database while other sessions are connected.  Alternatively you could use ALTER MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE at the beginning of your RESTORE script but that would be inconsiderate.

8.       Once the secondary is done restoring the most recent transaction log it was on, start the restore scripts you built in step 6. Once this step is done, you can inform the Reporting team that the database is ready to be queried.

9.       Cut and paste the files from steps 3 and 4 so they are back in the target folder for the copy job. 

10.   Re-enable the Copy job. Once step 8 is done, you can inform the Reporting team that the database is ready to be queried. 

You Really Should Stay Current on Cumulative Updates

 

I have encountered a Log Shipping error that I want to detail as an encouragement for staying current on SQL Server cumulative updates. First, a little background. The environment is SQL Server 2014 Standard edition with a physical machine as the primary and two secondaries, both virtual machines backed by a flash storage system.   SQL Server is on service pack 1, cumulative update 1. Log shipping had been configured in this environment for many months, but SQL Server 2008R2 was updated to SQL Server 2014 some months prior. The Log Shipping Restore job leaves the database in the Standby Mode so it can be queried for reporting purposes.

 

One evening I received the following error via database mail: “SQL Server Alert System: ’Severity 021’ occurred on \\MySecondary. An error occurred during recovery, preventing the database ‘MyDatabase’ from restarting. Diagnose the recovery errors and fix them or restore from a known good backup.” This was immediately followed by another email advising that the Log Shipping restore job had failed. Yet a third email was sent for error 9004 severity 16, explaining that an error occurred processing the log for “MyDatabase.” The message informed that I could restore from backup or rebuild the log. I connected to the SQL instance and saw that the database was in the restoring mode.

 

A quick internet search turned up the following two links.

https://support.microsoft.com/en-us/kb/2987585

https://support.microsoft.com/en-us/kb/2015753

 

The symptoms section of the first link seemed to fit my situation fairly well. I was unfamiliar with the fsutil command line utility. When I executed it against my environment the results indicated that the Bytes per Sector value was 512 and Bytes per Physical Sector returned “Not Supported.” The support article gave me two workarounds for addressing the issue.

  • Move the transaction log file at the destination to a drive that has “Bytes per Physical Sector” set as 512 bytes.Note The Standby file can still be located on the drive that has “Bytes per Physical Sector” set as 4096 bytes.
  • Restore the log backups without using the standby option. Instead of the STANDBY option, use the WITH NORECOVERY option during the restore operation.

 

Since my database was still in the restoring mode and the second URL indicated I had a damaged transaction log, I could not offline the database and relocate the file per the first recommendation. I also could not confirm how the bytes per sector value on the drive was configured anyway. The first article indicates that the issue was fixed in SQL Server 2014 SP1 CU2 and SQL Server 2012 SP2, CU7. Remember, I mentioned that the environment was on CU1 of SQL Server 2014 SP1. Unfortunately, that put me one update behind with respect to being protected from this situation.

 

Looks like I had one good course of action – restore from backup. I disabled the restore job, restored from a recent full backup and the most recent differential. I left the database in the restoring mode so I could re-enable the Log Shipping restore job. This would get the secondary back to Standby mode.

 

If you want to know when the latest updates for SQL Server are available, then I recommend saving the links below in your favorite web browser.  As this post demonstrates, be sure to read what the latest are about as soon as possible to see if they apply in your situation.

 

http://sqlserverbuilds.blogspot.com/

http://sqlserverupdates.com/

http://sqlblog.com/blogs/aaron_bertrand/default.aspx