Thursday, December 8, 2011

Log shipping 'Could not find a log backup file that could be applied to secondary database'

Today the log shipping that had been set up for a database started to fail. Found the following errors in the job history for LSRestore


***Error: The file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBname_201112081234.trn is too recent to apply to the secondary database 'DBname'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: The log in this backup set begins at LSN 842977000024386700001, which is too recent to apply to the database. An earlier log backup that includes LSN 841347000010148300001 can be restored.
Searching for an older log backup file. Secondary Database: 'DBname'
*** Error: Could not find a log backup file that could be applied to secondary database 'Dbname'.(Microsoft.SqlServer.Management.LogShipping) *** 


This means that the current log backup file that is being restored is too recent. A backup from a prior time needs to be applied first. 

So lets check what was the log file copied. The following query will give you the last log backup file that was copied

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]


Then check what was the last log backup file that was restored.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Then check the transaction backup file that was immediately copied after the last restored file. Sometimes one file is so big that the subsequent backup files get copied quickly. (sort by name and not by time). The file that is still being copied will show up in the correct order when you sort by name. But if you sort by date modified then it might show up at the end of the list.

So on the next restore attempt it will try to restore those files that have been successfully copied. And it will skip the file that is still being copied. So wait for that big file to be copied. 

It might also be the case that one of the log backup files did not get copied because it was deleted on the primary server. Or it got deleted from the secondary server before it was restored. The second case is unlikely so check the retention time on backup job that runs on primary server.

If you see file on primary that was not yet copied then copy that over and log shipping to start working again.




13 comments:

  1. So what do you do when both the Primary Server and the Secondary Server match as far as files but SQL transaction log shipping is still not working?

    ReplyDelete
  2. You need to find the reason or error which is causing the failure.
    On the primary there must be a job that starts with LS_Backup. Check the job history and look at the job steps and let me know what the error is.
    On the secondary you will find two jobs starting with LS_Copy and LS_Restore. Check the job step history and find the errors.

    Check what is failing. Is it backup or copy or restore? Then check the reason. If you want more help let me know.

    ReplyDelete
  3. The backup and copy from the primary to the secondary are showing completely succesful. On the Secondary server I am getting a no valid backup is found, but the data is the same in both the originating server and the secondary server.

    ReplyDelete
  4. Sometimes the copy job will complete successfully but won't copy any file.
    So check the time and name of the file that was last copied using the following query on the secondary server.
    SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

    Next check the job steps and paste the error here. There has to be some reason given in the job for the restore failure. I guess it's been more than a day since the log shipping has gone out of sync. So may be the backup file required for immediately has been deleted from the primary.

    Check each step in the restore job starting from the first one.

    First it tells us that it found the name of the last file that was restored. Next it will give you the first file name found in the directory. At this point it will try to restore it. If it is unsuccessful then it will say why. The most common reason is that this backup is too early. Which means that it missed restoring one of the previous backups which was been deleted already. In such situation just take a new full backup and restore it on secondary.

    ReplyDelete
  5. I appreciate your help with this and did follow through those steps and both the priamry server and the secondary server had the same logs on both of them. Unfortunately I could not spend any more time troubleshooting it and had to blow away the secondary DB and start from Scratch. Thanks you !

    ReplyDelete
  6. Ok I spoke too soon, it started up again, the error is as follows: "Error: Could not find a log backup file that could be applied to secondary database". When I compared the logs on both servers the copying and export is identical. I am starting to think SQL log shipping is severally broken.

    ReplyDelete
  7. Looks like it needs a log backup file to restore but that file has not yet been copied or has already been deleted from the primary. Please give me the output for the following two queries from secondary. Put a WHERE clause for that database name.

    SELECT * FROM [msdb].[dbo].[log_shipping_secondary]


    SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

    Next tell me the retention time for backups on Primary and secondary. Run the following query on Primary

    SELECT [primary_database] ,[backup_directory]
    ,[backup_retention_period] ,[last_backup_file]
    ,[last_backup_date]
    FROM [msdb].[dbo].[log_shipping_primary_databases] where Primary_database='Yourdbname'

    Next run the following query on secondary and give me the retention time for that database.

    SELECT [primary_database] ,[backup_source_directory]
    ,[backup_destination_directory] ,[file_retention_period]
    ,[last_copied_file]
    ,[last_copied_date]
    FROM [msdb].[dbo].[log_shipping_secondary] where Primary_Database='yourdbname'

    Lastly look at the backup directory on both primary and secondary and give me the file name and time of the earliest file.

    Now check what is the earliest file in the backup directory of the secondary server. If it is earlier than your full backup then we are good. But if you see that all backup files on secondary are after the full backup was taken then it means the backups files got deleted from primary before they could be copied.

    ReplyDelete
  8. My situation :
    There is a critical database at Primary datacenter and it is log-shipped this DB to 2 secondary servers which are geographically far away site and are connected to a MPLS dedicated Link.
    38 .trn files were accidently deleted that were not copied at secondary site, hence restore processes is halted at secondary site but copy process is working fine and is copying rest of the files after that incident.

    Size of DB is : 380 GB and N/W BW is 20Mbps. so it is not possible to backup DB at primary, then copy at secondary and restore and then reconfigure the logshipping maintenance plan. Secondly no previous full backup was taken(I don't know the reason) on which differential backup could be taken and then copied at secondary and resore.

    This is a new problem for me , please give suggestion how to overcome it
    thanks in advance

    P.S. : can deleting existing and creating a new maintenance plan in existing scenario helps??

    ReplyDelete
  9. Firstly if you deleted any log backup files from primary which were not yet copied to secondary then there is no other option but to re-initialize the secondary using a new full backup of the primary.

    Second, if log shipping was ever running properly then a full backup of the primary must have been taken. There is no way you can set up log shipping without taking a full backup primary.
    Even if we assume that a full backup was taken before, then that is still not a good option. Because you have to take a full backup and all subsequent differential backups if you have to resync using the differential backups. Which means you will require that full backup and all diff backup after that.

    So the only way out is to take a new full backup, copy it to an external hard drive and ship it overnight to the secondary location. This can be used to restore and resycn the secondary.

    Once you lose the log backups on primary there is no other solution.

    ReplyDelete
  10. my situation is not entirely the same. the new cloud server was in the process of being configured. in the meantime, ourorganization received the initial backup and .trn backup from log shipping. i defined the database on new cloud server. successfully, restored with standby the initial backup and first log file. i tried the next log file and got this

    Msg 4305, Level 16, State 1, Line 1
    The log in this backup set begins at LSN 321055000000823500001, which is too recent to apply to the database. An earlier log backup that includes LSN 320945000019176800001 can be restored.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE LOG is terminating abnormally.

    ReplyDelete
    Replies
    1. That message means that there was a log backup taken before the one that you are trying to apply. Check the backup history using following query and change the backup_start_date to a time before the full backup was taken. It should tell you the exact sequence of log backups taken after the full backup. Get all those log backup files and restore them.

      SELECT s.database_name,
      m.physical_device_name,
      CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
      s.backup_start_date, backup_finish_date,
      CASE s.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END as BackupType,
      s.server_name,
      s.recovery_model
      FROM msdb.dbo.backupset s
      inner join msdb.dbo.backupmediafamily m
      ON s.media_set_id = m.media_set_id
      WHERE s.database_name = 'yourdbname' and backup_start_date > '2013-05-31'
      ORDER BY backup_finish_date

      Delete
  11. Just drop by to say thank you very much. You know your article help the newbie like me a lot and save my day.
    Thank you.

    ReplyDelete
  12. Only reconfiguring the log shipping jobs has helped.

    ReplyDelete