sys.dm_exec_background_job_queue
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. |
In SQL Server 2005, this view returns information only for asynchronous update statistics jobs. For more information about asynchronous update statistics, see Index 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 |
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
