sys.index_resumable_operations is a system view that monitors and checks the current execution status for resumable Index rebuild or creation.
Applies to: SQL Server (2017 and newer), 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:
|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 ).|
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.
List all resumable index creation or rebuild operations that are in the PAUSE state.
SELECT * FROM sys.index_resumable_operations WHERE STATE = 1;