asked
Re-building index finished or not?
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][1] [1]: /storage/temp/1728-index-rebuiding.png
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
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][1] scripts [available here][2]. [1]:
http://sqlfool.com/ [2]:
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
If I would start from scratch with a new maintenance solution I would go with Ola Hallengren's solution. Michelle hasn't updated since 2011 and Ola's soultion is "certified" on SQL 2014, also he releases new features every now and then. Site:
ola.hallengren.com
It's not released yet, but the Midnight DBA's Sean & Jenn McCown are releasing a new set of scripts too. The reason I recommend Michelle's scripts is because they're easy to understand and implement. Ola's scripts can be a little overwhelming.
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][1] 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); [1]:
http://www.mssqltips.com/sqlservertip/3176/identify-completion-time-for-long-running-sql-server-processes-using-dynamic-management-objects/
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.
17 People are following this question.
