Hi all,
I'm new to MS-SQL (coming from the oracle world) so apologies for my (probably) obvious-noob question ;)
I've created a job that starts a snap-shot restore. This job gets triggered by a tsql script:
msdb.dbo.sp_start_job 'restore_snap';
This works fine, beside the sp_start_job will just fire-up the job ... and that's it ! It will not wait for its execution to finish or will output any errors that may occur during the restore ...
Not really nice, but ok ...
So I wrote another query that "monitors" the progress of the restore:
SELECT sj.name, sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sj.name = 'restore_snap'
Now, I wraped this code in a WHILE loop an check for the execution_date. And here is the problem. I want to check every 30 sec if the job is still running. The script should output every 30 sec: "script still running" or "finished"
So I used a "WAITFOR DELAY '00:00:30' " sleep combined with an "echo ....". BUT this doesn't work as expected. The script will NOT report the status every 30 secs but will wait until the WHILE loop has finished and then output all attempts. So if the script had run for 5 min ... it will wait for 5 min and then print 10 times "Script still running" afterwards.
This looks like a fault/bug rather than a feature to me ... but maybe I'm just using the wrong approach
Can you help me out ?