master..sqlbackup takes a long time to complete

turner732turner732 New JerseyPosts: 4 Bronze 1
edited October 29, 2014 1:21AM in SQL Backup Previous Versions
I am supporting a site using SQL Backup

We are log shipping 70 databases to a remote server using the stored proc below.
Lately the stored proc continues to run for a long time after all the logs for the database have been restored and moved to the logRestored directory.

Can you give any insight into what is happening?
Does this have anything to do with the Compact database the SQL backup logs to?

Thank you

ALTER PROCEDURE [dbo].[usp_restorelog] ( @DBNAME varchar(100) )
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
declare @RestoreStr varchar(2000), @RestStr varchar(2000)
SET @RestoreStr='-SQL "RESTORE LOG [XXX] FROM DISK = ''\aso_sql2008LogShippingLogsLOG_XXX_*.sqb'' WITH STANDBY = ''C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupUNDO_XXX.dat'', MOVETO = ''B:LogshippingLogsRestored'',ERASEFILES = 10, FILEOPTIONS = 1,DISCONNECT_EXISTING,MAILTO_ONERRORONLY=''[email protected]''"'
set @RestStr= Replace(@RestoreStr,'XXX',@DBNAME);

EXECUTE master..sqlbackup @RestStr, @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
SET @RestoreStr = 'SQL Backup failed for :'+@DBNAME
print @RestoreStr
--RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)



  • peteypetey Posts: 2,358 New member
    My best guess is that the ERASEFILES option is the cause of the error. You have ERASEFILES = 10, and your restored files are moved to 'B:LogshippingLogsRestored'. You have 70 databases. Assuming you take a log backup every hour, that's 16800 files present in the 'B:LogshippingLogsRestored'. SQL Backup will need to scan all 16800 files every time it restores a transaction log in order to determine which files to delete.

    What you can do is to move each file to its own database-specific folder e.g. MOVETO = ''B:LogshippingLogsRestored<DATABASE>''. In this way, each folder will only contain files for that specific database, and a maximum of 240 files. This will speed up the deletion process greatly.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • We are getting the same error. Wondering how was can speed things up to correct this error.

    SQL Backup log file

    -SQL "RESTORE LOG [********784] FROM DISK = '\****************.********.com********Backup1TranLogs********784********784_LOG_2008_2014.10.22_23.45.20.sqb' WITH ERASEFILES =1, STANDBY = 'D:********_DataMSSQL$********_********UNDO_********784.DAT',passw
    ord = 'XXXXXXXXXX' "


    10/27/2014 7:27:37 PM: Restoring ********784 (transaction logs) on ********_******** instance from:
    10/27/2014 7:27:37 PM: \****************.********.com********Backup1TranLogs********784********784_LOG_2008_2014.10.22_23.45.20.sqb

    10/27/2014 7:27:40 PM: RESTORE LOG [********784] FROM VIRTUAL_DEVICE = 'SQLBACKUP_1DC91F9A-6327-4822-8EC3-19EE23CE9CD7' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , STANDBY = N'D:********_DataMSSQL$********_********UNDO_********784.

    10/27/2014 7:27:41 PM: Thread 0 error:
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    10/27/2014 7:27:41 PM:
    10/27/2014 7:27:41 PM: SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
    10/27/2014 7:27:41 PM: SQL error 4326: SQL error 4326: The log in this backup set terminates at LSN 6781000001647700001, which is too early to apply to the database. A more recent log backup that includes LSN 6792000001416400001 can be restored.
  • peteypetey Posts: 2,358 New member
    The error indicates that the log backup you are trying to restore is too old (might had already been restored previously). You need to restore from a newer log backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.