Job step does not complete but no errors

Question

Job step does not complete but no errors

msmits

SSC Rookie

Points: 26
More actions
January 23, 2012 at 11:54 pm

#391383

I have a job step that creates a db backup and calls an sproc on another machine to restore it there (using a parameter for the file). The sproc restores the db and calls a job to perform post restore actions. Shortened, the code looks like this:
BACKUP DATABASE SDB TO DISK = @FilePath;
EXEC TargetMachine.master.dbo.RestoreSDB @BackupFile = @FilePath;
The target machine's sproc code looks like this (this is probably not relevant):
ALTER DATABASE SDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE SDB FROM DISK = @BackupFile
WITH FILE = 1, NOUNLOAD, STATS = 10, RECOVERY, REPLACE,
MOVE ...;
ALTER DATABASE SDB SET MULTI_USER;
EXEC msdb.dbo.sp_start_job 'PostRestoreSDB';
The job works fine when the db (a test db) is small. So I know the code/configuration works.
With a large db (400GB) the backup succeeds but the sproc is not called. The job history's text contains backup output followed by "The step succeeded" but no sproc (restore) output. It looks as though the restore call is not there at all. At the time the job runs both servers are hardly used. The big difference with the test db is the duration: The backup takes 3-4 hours.
The job has some additional steps which do get executed.
Both servers are 2008 and the db is in 2005 compatibility mode.
My feeling is that this is time related, but I can't find any settings related to job step duration. Is there any condition where a job step will stop prematurely without reporting errors?
Any help is greatly appreciated, I'm pretty much out of ideas at the moment.
Thanks,
Michel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gaurav Gosain-395499 Right there with Babe Points: 750 More actions · Answer 1

Hi

you can add different steps to your job, like in step 1 backup in step 2 restore and then in step 3 post restore thing. And in step properties make .

And on step go to advance and check Log output to file and include in history.

Thanks

Gaurav

Thanks,
GG;-)

msmits SSC Rookie Points: 26 More actions · Answer 2

Hi Gaurav,

I can't make two steps because of the dynamic filename that I cannot pass between steps.

I think I may have found the problem though.

The restore step uses a linked server, which has a query timeout. I've just done some testing and if the timeout fires, there is no mention of this in the SQL Agent log. The restore takes 3-4 hours and the default timeout is 10 minutes. I have changed the timeout for a test.

Thanks for the reply.

Cheers,

Michel