Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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.

Requires VIEW SERVER STATE permission on the server.

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.