This T-SQL code uses sp_help_job to check to current status of a specific job (JobID) and waits until its Idle.
Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server. More info?
First step is to create a simple SQL Agent Job called ‘WaitJob‘ that executes the statement:
WAITFOR DELAY '00:00:05'
Note: Be sure that ‘Ad Hoc Distributed Queries’ are enabled (More info?).
The T-SQL code checks the current status of a specific job (in this case ‘WaitJob’ and waits for it (by using the WAITFOR-statement):
Download the source code here.