Cancel Query on ReIndex - How much time for it to cancel

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  June 2, 2014 at 11:33 am

  #308751

  I executed the following command:

  DBCC DBREINDEX ('RDB_Bill.BILL_FACT', ' ', 70);

  The query was taking for ever so I cancelled it after 35 minutes

  I only has 1,289,573 records.

  I'm blocking two users who were performing SELECT Statements.

  The elapsed time is 47 minutes.

  How long will it take to cancel? At least 35 minutes or more?

  Thank you.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• SQL Guy 1

  SSCoach

  Points: 15979

  More actions

  June 2, 2014 at 11:44 am

  #1718289

  What version are you using ?

  If >= 2005 then use alter index ... with (online = on)

  As for your question, yes it may take around 35 min to undo.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  June 2, 2014 at 11:50 am

  #1718293

  Or Drop and recreate.

  Thanks.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jon.Morisi

  SSChampion

  Points: 12846

  More actions

  June 2, 2014 at 1:50 pm

  #1718346

  REBUILD has to rollback when cancelled.

  REORGANIZE does not.