1

I am in the process of deploying an update of our C# MVC application to our production environment. One of the steps involves addition of a primary key constraint to the identity column of a large table (~10GB). This is automatically creating a clustered index, and the ALTER TABLE statement is still running after two hours.

Our users will be returning to the application shortly, and I'm worried that if the ALTER TABLE statement is still running then they will see deadlock failures in the application.

Is there a way I can tell how far through the clustered index creation is? What will happen if I abort the ALTER TABLE command?

I am using SQL Server 2014 Standard.

Update: The alter table statement did eventually complete (without impacting users too much), but I'm still interested to learn more in case I run into a similar problem in the future.

  • 1
    Try to use SP_WHOISACTIVE.There's a column where you can see the "complete_percentage" of a process. Probably, when you kill the session, the process will rollback for some time. I don't think you will have problems. i'm saying this by experience doing the same thing. – Racer SQL Aug 31 '16 at 12:01
  • 1
    What version of sql server - 2012, 2014, 2016 and edition - standard, enterprise you are using ? – Kin Shah Aug 31 '16 at 13:26
0

Try to use SP_WHOISACTIVE.

Theres a column where you can see the Complete_percentage of a SPID/process.

You will not find problems by killing or stoping a clustered index creation, the problem is, you will need to wait for the RollBack proccess ( if you are waiting hours for the creation, the rollback will take hours too ).

enter image description here

  • 2
    I don't think percent_complete is available for index creates. Unlike DDL, I wouldn't expect the rollback of create index DML to take so long. – Dan Guzman Aug 31 '16 at 12:08
  • I tried SELECT * FROM sys.dm_exec_requests and that showed percentage complete as 0 and I've read elsewhere that it is not supported for index creation unfortunately. – thelem Aug 31 '16 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.