Log Shipping Fails

tlighttlight Posts: 6
edited November 18, 2006 3:01AM in SQL Backup Previous Versions
Hello,

My company has purchased 7 instances of Pro. I am trying to Log ship between the servers but they keep failing with this error:

Executed as user: VMCLUSTER\Administrator. SQL Backup job failed with exitcode: 1100 SQL error code: 4326 [SQLSTATE 42000] (Error 50000). The step failed.


I have the job set up to backup every 15 minutes and copy to a network share. The restore is set to go off every hour. It was working last night until around 2am but started failing. I noticed the log backup failed with this error at 11:30pm:

Executed as user: VMCLUSTER\administrator. SQL Backup job failed with exitcode: 145 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.



Heres the backup code:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-sql "BACKUP LOG [Command] TO DISK = ''\\192.168.11.30\backups\DB4\Cmd\LOG_(local)_Command_<DATETIME yyyymmdd_hhnnss>.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', COPYTO = ''\\Dc1\logshipping\Command\1'', COMPRESSION = 1, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END



Heres the restore code:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-sql "RESTORE LOG [Command] FROM DISK = ''\\Dc1\logshipping\Command\1\LOG_(local)_Command_*.sqb'' WITH STANDBY = ''R:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_Command.DAT'', MOVETO = ''\\192.168.11.30\backups\DB4\Cmd\re''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

Both servers are Windows 2003 server with Sql Server 2000 enterprise. SQL Backup version is 4.6.0.815
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    SQL error code 4326 indicates that the log file that was being restored was too early to apply to the database. One possibility is that the file has already been restored, but failed to be moved to the '\\192.168.11.30\backups\DB4\Cmd\re' folder. This will cause the file(s) to remain in the
    \Dc1\logshipping\Command\1 folder, and be picked up to be restored everytime.

    Look for the SQL Backup log files (default location is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>) that contains warnings indicating which backup log files were already restored but not moved. Manually move these files.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tlighttlight Posts: 6
    Thanks for the reply.

    Now it seems like everynight it fails from an error that says the log file is to late for the restore, and I copy one log file previous back into the shared folder and rerun the job successfully.

    Is it moving the file even if it fails, or is it somehow not copying the file over at all and causing the failure. If its not copying the file over, how do I determine if it isnt and setting it to try again? I have both jobs set to try 4 times before failing.

    Am I running the log shipping too much by shipping it every 15 minutes and restoring it every hour? Would it be better to slow it down (the db is about 30gbs big and grows rapidly)
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    and I copy one log file previous back into the shared folder and rerun the job successfully.
    Does the next to last file exist in your MOVETO folder i.e. \\192.168.11.30\backups\DB4\Cmd\re'' ?

    Can you pls check the SQL Backup log files to see if it attempted to restore that transaction log? The log files are found in <system drive>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> by default. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tlighttlight Posts: 6
    I'll check tomorrow if it happens again.

    Thanks
    · Share on Twitter
  • tlighttlight Posts: 6
    It wasnt in there, but it was in the directory that the logs should be backing up to then copying across. It had 3 small failed log files (~5kb) then it got a bigger one. I copied all 4 across and reran the job and it was sucessfull. This is why its failing. Should I slow down the log backups or could this be another problem?

    Thanks
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    THe problem appears to be a failure in copying the backup files over to the network share.

    Could you pls check the SQL Backup log files (by default in <system drive>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> ) for the corresponding backups, to see if this was indeed the case? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • tlighttlight Posts: 6
    I have this error in there:

    SQL Backup log file
    11/8/2006 7:15:01 PM: Backing up Command (transaction log) to:
    \\192.168.11.30\backups\DB4\Command\Log\LOG_(local)_Command_20061108_191501.sqb

    11/8/2006 7:15:01 PM: BACKUP LOG [Command] TO DISK = '\\192.168.11.30\backups\DB4\Command\Log\LOG_(local)_Command_20061108_191501.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', COPYTO = '\\Dc1\logshipping\Command', COMPRESSION = 1, THREADS = 1

    11/8/2006 7:15:01 PM: Error 510: Backup file exists. Will not overwrite. File name: (\\192.168.11.30\backups\DB4\Command\Log\LOG_(local)_Command_20061108_191501.sqb)


    and this:

    SQL Backup log file
    11/8/2006 8:15:01 PM: Backing up Command (transaction log) to:
    \\192.168.11.30\backups\DB4\Command\Log\LOG_(local)_Command_20061108_201501.sqb

    11/8/2006 8:15:01 PM: BACKUP LOG [Command] TO DISK = '\\192.168.11.30\backups\DB4\Command\Log\LOG_(local)_Command_20061108_201501.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', COPYTO = '\\Dc1\logshipping\Command', COMPRESSION = 1, THREADS = 1

    11/8/2006 8:15:01 PM: Error 510: Backup file exists. Will not overwrite. File name: (\\192.168.11.30\backups\DB4\Command\Log\LOG_(local)_Command_20061108_201501.sqb)


    and it continues with that...
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    That error indicates that a backup file of the same name already exists in the backup folder. Do you happen to have 2 jobs running at exactly the same time, to create transaction log backups for the same database (Command)?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.