Log Shipping Restore job does not end
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