I was using SQL Server 2014 Enterprise Edition and was trying to rebuild the index of a table. I was very confused by the UI that I am attaching below. As you can see, in the status column it says "Success" but in the Progress pane in the lower left corner it says it is still executing and the progress circle is still rolling. Can anyone clarify? As the "OK" and "Cancel" button are disabled, can I close the window? The task takes over 24 hours to run each time so I really don't want to take any risk to do it again. ![alt text] : /storage/temp/1728-index-rebuiding.png
If it says "Executing" then it's still executing. I would not suggest you use the GUI for rebuilding indexes, especially on tables that are so big that it takes 24 hours to complete (that's an extremely long period of time). Instead, I'd strongly suggest using scripts to get this done. I'd suggest taking a look at [Michelle Ufford's] scripts [available here]. :
If you want to track the progress and completion time, try this script from Tim Ford. It will give you estimates on progress. Also you can use the activity monitor, select your sql agent user and to see what is actually going on. This way you do not need to look at what the gui graphics say. But no.1 is to follow the advice Grant gave you. [Tim Ford - Identify Completion time] SELECT R.session_id, R.percent_complete, R.total_elapsed_time/1000 AS elapsed_secs, R.wait_type, R.wait_time, R.last_wait_type, DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time, ST.text, SUBSTRING(ST.text, R.statement_start_offset / 2, ( CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text) ELSE R.statement_end_offset END - R.statement_start_offset ) / 2 ) AS statement_executing FROM sys.dm_exec_requests R CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST WHERE R.percent_complete > 0 AND R.session_id <> @@spid OPTION(RECOMPILE); :
I concur with Grant and would further recommend that if it is taking THAT long you likely have lock contention issues delaying this as an index rebuild necessitates resource locks and you might want to consider monitoring access if not restricting it during maintenance windows. This isn't always possible but highly recommended and will greatly reduce the time required to do such tasks.