0

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

EXECUTE dbo.IndexOptimize
 @Databases = 'insight',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @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.

Improve this question
6
  • 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.
    – Erik Reasonable Rates Darling
    Commented Apr 8, 2018 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.
    – user2639010
    Commented Apr 8, 2018 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 Little
    Commented Apr 8, 2018 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.
    – user2639010
    Commented Apr 8, 2018 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 Little
    Commented Apr 9, 2018 at 0:49

1 Answer 1

Reset to default
1

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.

Improve this answer
15
  • 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?
    – user2639010
    Commented Apr 9, 2018 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.
    – Tony Hinkle
    Commented Apr 9, 2018 at 1:03
  • No blocking is going on. How do I check to see if a new index is being created?
    – user2639010
    Commented Apr 9, 2018 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.
    – Stephen Falken
    Commented Apr 9, 2018 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!
    – user2639010
    Commented Apr 9, 2018 at 1:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.