master..sqlbackup takes a long time to complete
turner732
Posts: 4 Bronze 1
I am supporting a site using SQL Backup 7.7.0.18
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
Bob
ALTER PROCEDURE [dbo].[usp_restorelog] ( @DBNAME varchar(100) )
as
BEGIN
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=''support@i-netsolutions.us''"'
set @RestStr= Replace(@RestoreStr,'XXX',@DBNAME);
EXECUTE master..sqlbackup @RestStr, @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
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)
END
END
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
Bob
ALTER PROCEDURE [dbo].[usp_restorelog] ( @DBNAME varchar(100) )
as
BEGIN
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=''support@i-netsolutions.us''"'
set @RestStr= Replace(@RestoreStr,'XXX',@DBNAME);
EXECUTE master..sqlbackup @RestStr, @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
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)
END
END
Comments
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
SQL Backup log file 7.6.0.29
-SQL "RESTORE LOG [********784] FROM DISK = '\********dbbackup.vip.********.********.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' "
ERRORS AND WARNINGS
10/27/2014 7:27:37 PM: Restoring ********784 (transaction logs) on ********_******** instance from:
10/27/2014 7:27:37 PM: \********dbbackup.vip.********.********.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.
DAT'
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8