Have you ever found yourself in a situation where you needed to prevent or alter the execution of a query, stored procedure, or SQL Agent job while another critical SQL Agent job is running? This is a common scenario, especially when you have a data staging table that needs to be fully loaded before downstream processes can access the data. In this blog post, we will discuss how to detect the state of a SQL Agent job and prevent the execution of other queries or logic until the job is completed.

SQL Agent Job State

The state of a SQL Agent job can be a bit complicated. The msdb database contains tables and views that record job and job step state. However, to ensure a fully consistent view of the data, you also need to query the sys.dm_exec_sessions view to check if the session is still active on the instance. This is because a failed job may appear as “failed” in the msdb-recorded job activity, while the underlying process is still actively rolling back. To be safe, it is recommended to check both msdb and sys.dm_exec_sessions when checking the job’s state.

Getting Job State From msdb

To retrieve the job state from msdb, you can use the following query:

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

Checking the job state in sys.dm_exec_sessions is a bit more complex. SQL Agent jobs appear in sys.dm_exec_sessions with program name values that encode the msdb.dbo.sysjob’s job_id value. You can extract the job_id value using the following query:

SELECT SUBSTRING(sp.[program_name], 32, 32)
FROM sys.dm_exec_sessions sp
WHERE sp.Status IN ('running', 'sleeping')

However, you will need a hex-to-GUID converter to match the job_id value. You can create a custom conversion function using the provided code.

Putting It All Together

Now that we have the necessary queries and functions, we can create a function to check if a specific SQL Agent job is running. The function will return 0 if the job isn’t running, 1 if it is, and NULL if the job doesn’t exist or if there is an error. Here is an example of how to create the function:

CREATE FUNCTION [dbo].[uf_IsSQLAgentJobRunning]
(
  @SQLAgentJobNameFragmentOrId VARCHAR(128)
)
RETURNS BIT
AS
BEGIN
    -- Function logic goes here
END

You can then use the function to perform actions based on the job’s state. For example, you can use a WHILE loop to wait for the job to complete before executing a critical process, or you can use the function in a WHERE clause to filter data based on the job’s state.

Remember to handle NULL values returned by the function, as they indicate that the job doesn’t exist or there was an error.

Conclusion

Preventing or altering the execution of SQL Server jobs can be a crucial requirement in certain scenarios. By understanding the job state and using the provided queries and functions, you can effectively control the execution of queries, stored procedures, or other SQL Agent jobs based on the state of a critical job. This ensures data consistency and prevents any issues that may arise from accessing incomplete or inconsistent data.

Disclaimer: The source code provided in this blog post is provided as is, without warranty. Use it at your own risk and ensure proper testing before implementing it in a production environment.