index_resumable_operations (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2017)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

sys.index_resumable_operations is a system view that monitors and checks the current execution status for resumable Index rebuild.
Applies to: SQL Server 2017 and Azure SQL Database

Column name Data type Description
object_id int ID of the object to which this index belongs (not nullable).
index_id int ID of the index (not nullable). index_id is unique only within the object.
name sysname Name of the index. name is unique only within the object.
sql_text nvarchar(max) DDL T-SQL statement text
last_max_dop smallint Last MAX_DOP used (default = 0)
partition_number int Partition number within the owning index or heap. For non-partitioned tables and indexes or in case all partitions are being rebuild the value of this column is NULL.
state tinyint Operational state for resumable index:

0=Running

1=Pause
state_desc nvarchar(60) Description of the operational state for resumable index (running or Paused)
start_time datetime Index operation start time (not nullable)
last_pause_time datatime Index operation last pause time (nullable). NULL if operation is running and never paused.
total_execution_time int Total execution time from start time in minutes (not nullable)
percent_complete real Index operation progress completion in % ( not nullable).
page_count bigint Total number of index pages allocated by the index build operation for the new and mapping indexes ( not nullable ).

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Example

List all resumable index rebuild operations that are in the PAUSE state.

SELECT * FROM  sys.index_resumable_operations WHERE STATE = 1;  

See Also

ALTER INDEX (Transact-SQL)
Catalog views (Transact-SQL) Object catalog views (Transact-SQL) sys.indexes (Transact-SQL) sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
sys.objects (Transact-SQL)
sys.key_constraints (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.partition_schemes (Transact-SQL)
Querying the SQL Server System Catalog FAQ