We create new index on our production database, with a few fields, our database size is around 300GB and it works for one hour and do not finished yet.

  • How can you estimate this operation?
  • If we will stop index creation; How time it will make rollback by your approximate estimation?
  • How can we check creation status in management studio?

On SQL Server Std Ed, 2005-2014, there is no way to get est time remaining (and Sporri's answer on this thread is simply incorrect, sorry).

This thread has an actual answer to this question: SQL Server: How to track progress of CREATE INDEX command?


This will give you the estimated time remaining

select session_id
      ,CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar (10)) + ' sec' as running_time
      ,CAST((estimated_completion_time %60000)/1000 as varchar (10)) + ' sec' as est_time_to_go
      ,dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
 from sys.dm_exec_requests

The rollback time is highly dependent on how busy the server is but will take at least the same time as the elapsed time, safe bet is elapsed time x 1.5

  • On SQL Server 2014, the est completion time is basically 'now' and is not useful.... May 22 '16 at 7:39

Your Answer

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