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?
Often, the main impetus for using xp_sqlagent_enum_jobs is to programmatically check if a SQL Server Agent job is currently running, and then either wait for it to complete or take some other action. While interactive tools are available for monitoring job status, there aren't many reliable and well-documented stored procedures, system tables, or DMVs suitable for use in automated, non-interactive scenarios.
Syntax and Usage:
xp_sqlagent_enum_jobs typically requires that minimum two input parameters are given and, a third optional parameter. However, since this procedure is undocumented, the exact nature and behavior of these parameters might vary slightly based on the version of SQL Server. Here is a general explanation of its parameters:
- Visibility Flag / is_sysadmin (Int): This parameter is usually an integer 0 or 1 to indicate if the executing user is a sysadmin (member of sysadmin role) and determines the scope of the job information returned by the procedure. A value of 1 indicates Yes and the procedure can return information for all jobs. A value of 0 means No in which case the procedure will return only those jobs that are owned by the executing user or jobs that the user has been granted permissions to view.
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., DOMAIN\Username) 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.
- Job ID (UNIQUEIDENTIFIER): Optional parameter to get information for a single job with a specific job ID.
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:
The procedure returns a result set with various columns providing details about each job. These details typically include:
- Job ID: A unique identifier for each job.
- Last Run Date: The date and time when the job was last executed.
- Next Run Date: The date and time when the job is scheduled to run next.
- Next Run Schedule ID: The identifier of the schedule according to which the job will run next.
- Requested To Run: Indicates whether the job has been requested to run immediately (outside its scheduled times).
- Request Source: Indicates the source of the run request.
- Request Source ID: The identifier of the request source.
- Running: Indicates whether the job is currently running.
- Current Step: The current step number the job is on (if it's a multi-step job).
- Current Retry Attempt: The number of the current retry attempt (in case the job is configured to retry on failure).
- 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_domain\your_login>'
/* OR */
EXEC master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
Checking for Running Jobs
You can use xp_sqlagent_enum_jobs to check if there are any jobs currently running on the SQL Server Agent.
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_domain\your_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_domain\your_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