sys.dm_exec_background_job_queue (Transact-SQL)

 

Updated: August 9, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

NOTE!! To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_background_job_queue.

Column nameData typeDescription
time_queueddatetimeTime when the job was added to the queue.
job_idintJob identifier.
database_idintDatabase on which the job is to execute.
object_id1intValue depends on the job type. For more information, see the Remarks section.
object_id2intValue depends on the job type. For more information, see the Remarks section.
object_id3intValue depends on the job type. For more information, see the Remarks section.
object_id4intValue depends on the job type. For more information, see the Remarks section.
error_codeintError code if the job reinserted due to failure. NULL if suspended, not picked up, or completed.
request_typesmallintType of the job request.
retry_countsmallintNumber of times the job was picked from the queue and reinserted because of lack of resources or other reasons.
in_progresssmallintIndicates whether the job has started execution.

1 = Started

0 = Still waiting
session_idsmallintSession identifier.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

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 typeobject_id1object_id2object_id3object_id4
Asynchronous update statisticsTable or view IDStatistics IDNot usedNot 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  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
Statistics
KILL STATS JOB (Transact-SQL)

Community Additions

ADD
Show: