Log Shipping Restore job does not end

jrandalljrandall Posts: 35
edited March 16, 2008 9:14PM in SQL Backup Previous Versions
I have run into an issue with SQL Backup 5.3xx log shipping restore jobs.

The following code runs successfully in a SQL Agent job, however the job stays in an “Executing” state, which means it only runs once. Attempting to stop the job from SSMS failed. I had to stop the SQL Server service to stop the job. This behaviour has occured in 2 separate jobs (one created from the SQL Backup Log Shipping Wizard, and one created manually) running on 2 different servers (both source and destination), for 2 different databases.

We are running SQL Server 2005 Enterprise Edition, sp2, cumulative update 4 on Windows Server 2003 R2, sp2. Any ideas on why this behavior is occuring, or possible solutions would be greatly appreciated.
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [xyzDB] FROM DISK = ''U:\SQL_BKUP\LogShip\LOG_xyzDB_*.sqb'' WITH NORECOVERY
       , MOVETO = ''U:\SQL_Bkup\xyzDB'' "'
       , @errorcode OUT
       , @sqlerrorcode OUT; 

IF (@errorcode >= 500) OR (@sqlerrorcode <> 0) 
  BEGIN 
     RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode) 
  END
GO
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Do you mean that every invocation of the job results in the job staying in the 'Executing' state indefinitely, or has it occurred only twice so far?

    If the former, if you were to run the restore command directly, not via SQL Agent, does it complete successfully?

    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
  • jrandalljrandall Posts: 35
    Yes, every invocation causes it to stay in Executing state indefinitely. I am also seeing an issue where backup jobs seem to overwhelm the server.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Does the restore complete when ran directly, and not via SQL Agent?

    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
  • jrandalljrandall Posts: 35
    Yes. The restore operation completes whether run in SSMS or in a job, but when run in a job, the job does not stop.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    SQL Backup generates a log file when it completes its task. The default folder where these log files are stored is <system drive>\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>. Could you please check if the log files were generated for the jobs ran by SQL Server Agent? If so, could you please send me a couple of those files?

    If no log files were generated, could you please run a Profiler Trace when one of the jobs is running, and send me the trace file?

    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
Sign In or Register to comment.