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. |
This view returns information only for asynchronous update statistics jobs. For more information about asynchronous update statistics, see Using Statistics to Improve Query Performance.
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 |
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
