I'm running an index rebuild using Ola Hallengren's script below.

EXECUTE dbo.IndexOptimize
 @Databases = 'insight',
 @FragmentationLow = NULL,
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @LogToTable = 'Y'

It has been running for almost 2 days. How bad would it be to kill the job? The index it is running against is 78% fragmented. The table at issue has 111,543,114 rows.

  • If the command that's running is a reorg, you can just kill it. If it's a rebuild, it'll have to roll back, which is single threaded, and may take a very long time. This might be a good time to stop blindly rebuilding indexes. Apr 8 '18 at 17:18
  • Maybe I should have added a time out in the T-SQL so it doesn't run for ever. The real question is kill or not as it is doing a rebuild and not a reorg. Apr 8 '18 at 18:26
  • Is it an online rebuild or offline of this index?. How large is that index? It's not a huge amount of rows, wondering at the size. Any messages in your error log look related?
    – kendra
    Apr 8 '18 at 21:38
  • It is an offline rebuild. There are 4 indexes on the table for a total of 24 GB. No errors in the log. The table is 63 GB. Apr 8 '18 at 22:25
  • How big is the index it's working on? Are you sure it's not blocked and has been working on the individual index for that long? Tony's suggestion to look at sp_whoisactive will help clear that up. Even if your storage is REALLY slow, I would expect to see messages in the log about the storage not responding at that runtime.
    – kendra
    Apr 9 '18 at 0:49

You should be able to see if it's doing a reorg or a rebuild by using sp_whoisactive or something similar and inspecting the sql_text column.

If you kill it, you will not lose the work it has done on previous indexes that were defragmented as part of this script. Those are done and committed.

As for the current index it's defragmenting, if it's doing a reorg, the progress it has made will not be lost if you kill it. If it's a rebuild, which seems to be the case given the data you've provided, it's still OK to kill it, but you will lose any progress that has been made.

  • Ran sp_whoisactive and I don't see my job as running. So if I kill the TSQL running, will there still be an index on the table? Apr 9 '18 at 0:55
  • It should show it, so I'm not sure what's going on. Index rebuild creates an entirely new index, and when it is done, it drops the old index and renames the new index. So if you kill it, it will roll back all of the work it did to create the new index. It should not affect the old index. If the index is somehow lost, you are one very unlucky person as that would be quite a significant defect. Apr 9 '18 at 1:03
  • No blocking is going on. How do I check to see if a new index is being created? Apr 9 '18 at 1:14
  • Are you running an agent job, or running the query in an SSMS window from your machine? Regardless, sp_WhoIsActive WILL show an active index reorg/rebuild. Have you checked dbo.CommandLog (wherever you created that)? That will show the start and end time for the processing of the indexes on the table in question. Apr 9 '18 at 1:21
  • Running the query from SSMS on my machine. sp_whoisactive doesn't show an active index reorg/rebuild going on. I've looked at the CommandLog and it shows: the index job ran. No new records have been added to the CommandLog since Noon on 6 April. No idea what is going on. Arg! Apr 9 '18 at 1:28

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.