Any suggestions? Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.dm_exec_background_job_queue (Transact-SQL)

 

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

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

System_CAPS_noteNote

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

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.

pdw_node_id

int

The identifier for the node that this distribution is on.

Applies to: Azure SQL Data Warehouse Public Preview, Parallel Data Warehouse

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 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

Community Additions

ADD
Show:
© 2016 Microsoft