title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_exec_background_job_queue (Transact-SQL) |
sys.dm_exec_background_job_queue (Transact-SQL) |
rwestMSFT |
randolphwest |
02/24/2023 |
sql |
system-objects |
reference |
|
|
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-asa-pdw]
Returns a row for each query processor job that is scheduled for asynchronous (background) execution.
Note
To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_exec_background_job_queue
. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
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 | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
On [!INCLUDEssNoVersion_md] and SQL Managed Instance, requires VIEW SERVER STATE
permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader##
server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE
permission on the database, or membership in the ##MS_ServerStateReader##
server role is required.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
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 [!INCLUDEssNoVersion].
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