Workaround: Control SQL Agent Job Steps based on query outcome.
Below workaround will help you incase if you wanna control SQL agent job steps based on a SQL query output. For eg: Suppose you have to check if any backup jobs are running before executing a resource intensive job step. Here is how we can work around and control job steps. You can modify below steps anyway based on your needs as I have just used below strategy as an example.
Methord Summary
- Run the query to check if the backup is running. Query to check backup status is
select count(*) from sys.dm_exec_requests where command like '{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}BACKUP{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}'
The result will be > zero if the backup is running. and zero if no backups are running. - Execute the resource intensive task if the above query returned ZERO else exit the job reporting success.
Steps
1. Create a job with first step to check the backup status.
2. Use below query to check backup status and thereby navigate the job steps by making it raise an error causing the job step to fail if the backup is running.
DECLARE @bkpstatus int
select @bkpstatus=count(*) from sys.dm_exec_requests
where command like '{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}BACKUP{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}'
/*Below if statement will trigger
an error if backup is running */
if @bkpstatus > 0
BEGIN
RAISERROR('50005 A Backup is currently running.',16, -1, @@servername )
END
else
BEGIN
select @@servername
END
3. Go to advanced option for this job step and set as “quit the job reporting success” if the step fails, and to proceed to next step if the step succeeds which will trigger the resource intensive query. Instead of setting “Quite the job reporting success”, You can also make it move to a step that could send some email alert saying the task was aborted since a backup was currently running.
4. Create the next step that would be triggering the resource intensive query.
