SQL Server Non Clustered Index CREATE taking forever to cancel

Question

I ran a CREATE NON CLUSTERED INDEX command for 1.5 hours. Decided to cancel it because it was stopping other things running. The command has now been running for 22 hours. What is it doing that takes so long to roll back?

The table is 2 billion rows and the clustered index is a columnstore.

The version of SQL Server is 2022.

There are no other jobs running on it (although that is only a recent development).

Answer 1

Hi @Nick Ryan

It will take a while if you have 2 billion rows, the time required to create an index is proportional to the number of rows you have.

What is it doing that takes so long to roll back?

Have you check whether SQL Server has an I/O problem?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

It will definitely take a while. The amount of time it runs is not directly related to how long it will take to roll it back if you kill it. But you can use this query during those times to see how it is doing.

If you are on SQL 2017 and above consider using RESUMABLE indexing, if your edition supports it. Then you can just suspend it and resume it later on.

select 
	dt.transaction_id,
	DB_NAME(dt.database_id) as dbname,
	dt.database_transaction_log_bytes_used,
	dt.database_transaction_log_bytes_reserved
from sys.dm_tran_database_transactions AS dt
INNER JOIN sys.dm_tran_session_transactions st on dt.transaction_id = st.transaction_id
WHERE st.session_id = <replace with spid>

Answer 3

Rollback is a lot slower than commit. So if you cancel 1 1/2 hours in, it will take several hours to rollback. Don’t restart the sql as this cause the rollback to start over on restart, losing all time you have waited.