Introduction
Sometimes you find yourself in need of a way to prevent or alter the execution of a query, stored
procedure, or SQL Agent job while some (other) crtitical SQL Agent job is executing. A common use case for this is the situation where the critical SQL Agent job is used to load a data staging table, which will be consumed downstream by targets dependent on a completely guaranteed-consistent view of the data. In this case, it is necessary to wait on the job to complete before accessing the data. One way to accomplish this is to detect the state of the critical SQL Agent job, and either wait or not execute the downstream query or logic.
SQL Agent Job State
SQL Agent job state is a bit of a complicated beast. The msdb database contains a set of tables and views that record job and job step state; however, for a full consistency guarantee that you are absolutely not running your critical downstream query or process until the job is fully completed, committed and/or rolled back, you must also query the sys.dm_exec_sessions to ensure the session is not still active on the instance. This is because it is possible for a failed job to appear as "failed" and thus inactive in msdb-recorded job activity, while the underlying process is still actively rolling back. My gut on this is that there may be other transitory states that act similarly, though admittedly I have no proof of this. Better safe than sorry; so I'm going to check both msdb and sys.dm_exec_sessions when I check the job's state.
Getting Job State From msdb - Easy!
This query will return the msdb.dbo.sysjobs job_id and name of any query currently executing:
SELECT j.job_id , j.name FROM msdb..sysjobs j WHERE EXISTS ( SELECT 1 FROM msdb..sysjobsteps sj INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id WHERE sj.job_id = j.job_id AND ja.start_execution_date <= GETDATE() AND ja.stop_execution_date IS NULL AND ja.session_id = ( SELECT MAX(ja2.session_id) FROM msdb..sysjobactivity ja2 WHERE ja2.job_id = j.job_id ) )
Getting Job State From sys.dm_exec_sessions - Harder!
Checking the same job's state in sys.dm_exec_sessions is a bit harder. SQL Agent jobs appear in sys.dm_exec_sessions with program name values that look like this:
"SQLAgent - TSQL JobStep (Job 0x975430A7DC577B44B949EAAC92123325 : Step 2)"
The hexadecimal number encodes the msdb.dbo.sysjob's job_id value. The hex number always begins at the 32nd character of the sys.dm_exec_sessions program_name value, and is always 32 characters long. We can therefore extract it for running or sleeping processes with:
SELECT SUBSTRING(sp.[program_name], 32, 32) FROM sys.dm_exec_sessions sp WHERE sp.Status IN ( 'running', 'sleeping' )
but then we need a hex-to-GUID converter to match it to a job_id value. This is provided by the following custom conversion function:
CREATE FUNCTION [dbo].[uf_HexToChar] ( @binaryValue VARBINARY(100) , @numBytes INT ) RETURNS VARCHAR(200) AS BEGIN DECLARE @i VARBINARY(10) , @hexdigits CHAR(16) , @s VARCHAR(100) , @h VARCHAR(100) , @currentByte SMALLINT SET @hexdigits = '0123456789ABCDEF' SET @currentByte = 0 SET @h = '' -- process all bytes WHILE @currentByte < @numBytes BEGIN SET @currentByte = @currentByte + 1 -- get first character of byte SET @i = SUBSTRING(CAST(@binaryValue AS VARBINARY(100)), @currentByte, 1) -- get the first character SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1)) -- shift over one character SET @i = @i / 16 -- get the second character SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1)) + @s -- build string of hex characters SET @h = @h + @s END RETURN(@h) END GO
Now we can join sys.dm_exec_sessions to msdb.dbo.sysjobs and match the two, and return any jobs found in sys.dm_exec_sessions with:
SELECT j.job_id, j.name FROM msdb..sysjobs j WHERE dbo.uf_HexToChar(j.job_id, 16) IN ( SELECT SUBSTRING(sp.[program_name], 32, 32) FROM sys.dm_exec_sessions sp WHERE sp.status IN ( 'running', 'sleeping' ) )
Hopefully the relatively small number of processes and jobs will minimize the performance issue with the computed-to-computed non-indexed join involved.
Putting It All Together
So now what I want is a function I can call to see if a particular SQL Agent job is running. It will return 0 if the job isn't running, 1 if it is, and NULL if the job doesn't exist or some other bad-input condition occurs.
I want to pass either a SQL Agent job ID GUID, or the job's name (or a uniquely-identifying fragment thereof). For this I can pass a varchar parameter, and if it matches a GUID value I will assume it's a job ID, otherwise that it is part or all of the job name.
Hmm, since I don't have SQL 2012's TRY_CONVERT() function yet, I'll need another helper function to test if the passed string is a GUID or not:
CREATE FUNCTION uf_IsGUID ( @Value VARCHAR(MAX) ) RETURNS BIT AS BEGIN DECLARE @rtn BIT SET @rtn = 0 IF @Value IS NULL OR LOWER(@Value) LIKE '[a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0 -9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0-9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0-9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0 -9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9] [a-f0-9]' ESCAPE '\' SET @rtn = 1 RETURN @rtn END GO
Note that NULL *is* a valid GUID and can be converted to a UNIQUEIDENTIFIER !
Now, I can put together my prime objective:
CREATE FUNCTION [dbo].[uf_IsSQLAgentJobRunning] ( @SQLAgentJobNameFragmentOrId VARCHAR(128) ) RETURNS BIT AS BEGIN DECLARE @rtn BIT , @isGUID BIT -- assume job is not running; must prove otherwise! SET @rtn = 0 -- scrub inputs SET @SQLAgentJobNameFragmentOrId = NULLIF(@SQLAgentJobNameFragmentOrId, LTRIM(RTRIM(''))) -- return NULL if NULL or blanks passed (sanity check) IF @SQLAgentJobNameFragmentOrId IS NULL RETURN NULL -- was a job_id passed ? SET @isGUID = dbo.uf_IsGUID(@SQLAgentJobNameFragmentOrId) -- if no matching job found by job_id or name, return NULL IF 1 <> ( SELECT COUNT(*) FROM msdb..sysjobs j WITH ( NOLOCK ) WHERE ( @isGUID = 0 AND j.name LIKE '%' + @SQLAgentJobNameFragmentOrId + '%' ) OR ( @isGUID = 1 AND j.job_id = @SQLAgentJobNameFragmentOrId ) ) RETURN NULL -- if both msdb and dm_exec_sessions considers the job as "running", return 1 IF EXISTS ( SELECT 1 FROM msdb..sysjobs j WHERE dbo.uf_HexToChar(j.job_id, 16) IN ( SELECT SUBSTRING(sp.[program_name], 32, 32) FROM sys.dm_exec_sessions sp WHERE sp.Status IN ( 'running', 'sleeping' ) ) AND ( ( @isGUID = 0 AND j.name LIKE '%' + @SQLAgentJobNameFragmentOrId + '%' ) OR ( @isGUID = 1 AND j.job_id = @SQLAgentJobNameFragmentOrId ) ) ) AND EXISTS ( SELECT 1 FROM msdb..sysjobs j WHERE EXISTS ( SELECT 1 FROM msdb..sysjobsteps sj INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id WHERE sj.job_id = j.job_id AND ja.start_execution_date <= GETDATE() AND ja.stop_execution_date IS NULL AND ja.session_id = ( SELECT MAX(ja2.session_id) FROM msdb..sysjobactivity ja2 WHERE ja2.job_id = j.job_id ) ) ) SET @rtn = 1 RETURN @rtn END GO
Usage
SELECT [dbo].[uf_IsSQLAgentJobRunning]('My Job name here')
returns 1 if 'My Job name here' is running, 0 if it is not.
SELECT [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756')
returns 1 if the job with job_id FEFB4793-22F1-4744-916C-E672A7D14756 is running, 0 if it is not.
Now you can do things like:
WHILE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 1 BEGIN WAITFOR DELAY '00:01:00' END EXEC some critical process
or:
SELECT * FROM SomeDataTable WHERE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 0
or:
WHILE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 0 BEGIN --process row(s) or a batch of data as long as job hasn't restarted END
or, in an initial job step, wait for one job to complete before beginning another:
WHILE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 1 BEGIN WAITFOR DELAY '00:00:01' END
or how about a listener job C that has a job step that listens for the end of job A and starts job B or when job A has completed?
WHILE [dbo].[uf_IsSQLAgentJobRunning]('A') = 1 BEGIN WAITFOR DELAY '00:00:01' END EXEC msdb.dbo.sp_Start_job @job_name='B'
Sure, you could start job B from A directly in a final step in A, but if A fails, depending on the severity level of the error, B might not be started. Ditto if A is cancelled. This gives you a foolproof wait to start job B regardless of the final state of job A.
Disclaimer
The source code is provided to you as is, without warranty. There is no warranty for the program, expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose and non infringement of third party rights. The entire risk as to the quality and performance of the program is with you. Should the program prove defective, you assume the cost of all necessary servicing, repair and correction.