Understanding and Using xp_sqlagent_enum_jobs in SQL Server

What is xp_sqlagent_enum_jobs?

xp_sqlagent_enum_jobs is an undocumented, extended stored procedure in Microsoft SQL Server. It is used to provide information about the jobs that are currently managed by the SQL Server Agent. This extended stored procedure is used in some internal procedures (e.g. sp_get_composite_job_info) by SQL Server, but it can also be used in custom scripts or applications to obtain details about SQL Agent jobs, especially in automated scripts or applications that need to track job status or performance. For example:

How to start SQL Server job using TSQL and wait for it to complete?

Syntax and Usage:

In my experience though, xp_sqlagent_enum_jobs  seems to accept not only 0 or 1 but various integer values, both positive and negative.

• Job Owner (Sysname): This is a string parameter representing the user in the context of whom the job information is requested. It's often provided in the format of a domain and username (e.g., DOMAINUsername) or 'sa' if a DBA is running this procedure. This parameter is used along with the the visibility flag to determine which jobs are visible to the specified user based on their permissions.

Again in my experience, xp_sqlagent_enum_jobs  seems to accept any value, including invalid or non-existent usernames.

If you are getting Job ID value from the msdb.dbo.sysjobs table, make sure to convert its value to a UNIQUEIDENTIFIER data type.

Basic Examples:

In this example, 1 is the visibility flag, and 'sa' represents the user.

EXEC master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

 

 

To get information about a specific job:

 

DECLARE @job_name NVARCHAR(100) = 'DatabaseIntegrityCheck - USER_DATABASES'
DECLARE @job_id UNIQUEIDENTIFIER
SELECT @job_id = job_id FROM   msdb..sysjobs WHERE  NAME = @job_name
EXEC master.dbo.Xp_sqlagent_enum_jobs 1, 'sa', @job_id 

 

When using xp_sqlagent_enum_jobs, consider the following aspects:

Permissions: The actual jobs returned can depend on the permissions of the user executing the procedure or the user specified in the parameters.

Security Risk: As an undocumented feature, using this procedure can pose a security risk, especially if used improperly or without understanding its impact.

Version Dependence: The behavior and availability of xp_sqlagent_enum_jobs can change without notice in different versions of SQL Server.

Results/Output Columns:

1. Job ID: A unique identifier for each job.
2. Last Run Date: The date and time when the job was last executed.
3. Next Run Date: The date and time when the job is scheduled to run next.
4. Next Run Schedule ID: The identifier of the schedule according to which the job will run next.
5. Requested To Run: Indicates whether the job has been requested to run immediately (outside its scheduled times).
6. Request Source: Indicates the source of the run request.
7. Request Source ID: The identifier of the request source.
8. Running: Indicates whether the job is currently running.
9. Current Step: The current step number the job is on (if it's a multi-step job).
10. Current Retry Attempt: The number of the current retry attempt (in case the job is configured to retry on failure).
11. State: The current state of the job.

What does it mean that xp_sqlagent_enum_jobs is undocumented?

xp_sqlagent_enum_jobs is indeed an undocumented extended stored procedure in Microsoft SQL Server. This means that it is not officially documented or supported by Microsoft in their public documentation. In essence, this raises several important considerations:

• Lack of Official Documentation: Since there's no official documentation, detailed information about its parameters, behavior, and potential changes in different versions of SQL Server are not publicly available through Microsoft's channels.

• Potential for Change: Microsoft may change or remove undocumented features in any new release or update of SQL Server without prior notice. This can lead to compatibility issues or sudden breaks in functionality in systems that rely on these features.

• Limited Support: If you encounter issues while using undocumented features, finding solutions can be more challenging as these features are typically not covered by standard support channels.

• Use with Caution: It's advisable to use undocumented features with caution, especially in production environments. They might be less stable or tested compared to officially supported features.

• Alternative Approaches: For long-term stability and support, it's often better to seek alternative, documented features or methods to achieve the same goals.

• Community Knowledge: Sometimes, information about undocumented features can be found through community forums, blogs, or from SQL Server experts who share their findings. However, this information is based on personal experiences and might not be universally applicable.

Usage Scenerios:

Here are a few example scenarios of how xp_sqlagent_enum_jobs might be used:

Basic Usage to List All SQL Agent Jobs

This is the simplest form of using xp_sqlagent_enum_jobs to get a list of all SQL Agent jobs with their details.

EXEC master.dbo.xp_sqlagent_enum_jobs 1, '<your_domainyour_login>'

/* OR */

EXEC master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

IF OBJECT_ID('tempdb..#RunningJobs') is not null DROP TABLE #RunningJobs;
go
CREATE TABLE #RunningJobs (
    job_id UNIQUEIDENTIFIER NOT NULL,
    last_run_date INT NOT NULL,
    last_run_time INT NOT NULL,
    next_run_date INT NOT NULL,
    next_run_time INT NOT NULL,
    next_run_schedule_id INT NOT NULL,
    requested_to_run INT NOT NULL, -- boolean
    request_source INT NOT NULL,
    request_source_id sysname COLLATE database_default NULL,
    running INT NOT NULL, -- boolean
    current_step INT NOT NULL,
    current_retry_attempt INT NOT NULL,
    state INT NOT NULL
)

INSERT INTO #RunningJobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '<your_domainyour_login>'
SELECT * FROM #RunningJobs WHERE running = 1
IF OBJECT_ID('tempdb..#RunningJobs') is not null DROP TABLE #RunningJobs;
go

Finding Jobs with Specific Conditions

You can filter the results to find jobs with specific conditions, like jobs that have failed to run, or jobs scheduled to run at a specific time.

SELECT * FROM #RunningJobs WHERE last_run_outcome <> 1 -- Replace with your specific condition

Wait for a SQL Server job to complete before proceeding

Precautions and Best Practices:

• Use a Dedicated Account: When specifying the user account (<your_domainyour_login>), it's best to use an account with appropriate permissions.

• Temporary Tables: In the examples, I've used temporary tables (#RunningJobs) to store the output of xp_sqlagent_enum_jobs. This is a common practice to make data handling easier.

• Cleanup: Always remember to clean up temporary tables to avoid unnecessary consumption of resources.

• Testing: Thoroughly test these scripts in a non-production environment first to ensure they work as expected in your specific SQL Server setup.

• Monitoring and Logging: When using such procedures in scripts or applications, implement adequate logging and error monitoring.

• Seek Alternatives: While I haven't tried or tested the following myself, here is an article discussing one of the possible alternatives:

xp_sqlagent_enum_jobs alternative