Log Shipping Restore job does not end

jrandall

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

petey

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.

jrandall

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.

petey

Does the restore complete when ran directly, and not via SQL Agent?

Thanks.

jrandall

Yes. The restore operation completes whether run in SSMS or in a job, but when run in a job, the job does not stop.

petey

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.