sys.dm_exec_background_job_queue (Transact-SQL)

Returns a row for each query processor job that is scheduled for asynchronous (background) execution.

Column name

Data type

Description

time_queued

datetime

Time when the job was added to the queue.

job_id

int

Job identifier.

database_id

int

Database on which the job is to execute.

object_id1

int

Value depends on the job type. For more information, see the Remarks section.

object_id2

int

Value depends on the job type. For more information, see the Remarks section.

object_id3

int

Value depends on the job type. For more information, see the Remarks section.

object_id4

int

Value depends on the job type. For more information, see the Remarks section.

error_code

int

Error code if the job reinserted due to failure. NULL if suspended, not picked up, or completed.

request_type

smallint

Type of the job request.

retry_count

smallint

Number of times the job was picked from the queue and reinserted because of lack of resources or other reasons.

in_progress

smallint

Indicates whether the job has started execution.

1 = Started

0 = Still waiting

session_id

smallint

Session identifier.

Permissions

Requires VIEW SERVER STATE permission on the server.

Remarks

This view returns information only for asynchronous update statistics jobs. For more information about asynchronous update statistics, see Statistics.

The values of object_id1 through object_id4 depend on the type of the job request. The following table summarizes the meaning of these columns for the different job types.

Request type

object_id1

object_id2

object_id3

object_id4

Asynchronous update statistics

Table or view ID

Statistics ID

Not used

Not used

Examples

The following example returns the number of active asynchronous jobs in the background queue for each database in the instance of SQL Server.

SELECT DB_NAME(database_id) AS [Database], COUNT(*) AS [Active Async Jobs]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id;
GO

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

Execution Related Dynamic Management Views and Functions (Transact-SQL)

KILL STATS JOB (Transact-SQL)

Concepts

Statistics