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.

Permissions

Requires VIEW SERVER STATE permission on the server.

Remarks

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

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
Execution Related Dynamic Management Views and Functions
KILL STATS JOB (Transact-SQL)
sys.dm_exec_background_job_queue_stats

Other Resources

Index Statistics

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added the Examples section.