Post reply

index rebuild offline manage db downtime

  • October 18, 2010 at 12:49 pm

    #149031

    Hi guys, I have a question for you:

    I am doing an index rebuild offline weekly.

    I have a Service that runs 24/7 and inserts articles in the db.

    Do I need to run a script that will stop this Service during the offline rebuild?

    Is there a queue that is generated with all the requests or because of the lock on those tables I will just have some errors?

  • October 18, 2010 at 10:58 pm

    #1238830

    You need not stop the service which connects to the database whose index is being rebuilt offline. However during offline reindex locks will be held on the object and any other process trying to connect to it will be blocked.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • October 19, 2010 at 2:50 am

    #1238893

    If your environment is 24x7 and having difficulty for downtime; you can check for ONLINE option while reindexing with ALTER INDEX command. Online index operations are available ONLY in SQL Server Enterprise Edition.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • October 19, 2010 at 9:15 am

    #1239114

    I cant do ONLINE due to the tables that have data types TEXT, NTEXT that can not have their indexes rebuilt online.

    The question was, do I need to stop the services that interrogate/insert into the db while i do this rebuild?

    I understand there is a lock, but will it be a queue with the request on those tables that will start after the rebuild is done?

  • October 19, 2010 at 10:23 am

    #1239178

    what is the service you are talking about? maybe the company / dev that wrote the service would have the answer you are looking for.....

    every app / service acts differently based on how it was written.

  • October 19, 2010 at 1:15 pm

    #1239294

    lol

    I wrote the Service.

    It does plenty of things among all, also inserts in the db. But not the Service i sthe question here, any kind of inserts are the question. Doesn't matter if they are trigger by a service or by a user or who knows what else.

    I was wondering should i stop the service, meaning the inserts requests will they stay in sort of a queue until the lock is release and they will be executed after?

  • October 19, 2010 at 2:51 pm

    #1239359

    You no need to stop any service while you do rebuild indexes offline.

    schedule it when your server is less productive or less no.of connection depends on your business.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • October 20, 2010 at 9:11 am

    #1239755

    unless you have an infinite timeout set on this service (and other things hold true), YES you WILL need to stop this service. You can throw all you want at the database, but anything affected by this index maintenance will BLOCK until such time as the index rebuild is complete. And if it is blocked beyond the timeout period, it will disconnect and throw an error.

    Some potential solutions:

    1) reorg instead of rebuild the index. note this can cause extremely large tlogs for severely fragmented indexes.

    2) move the BLOB columns off to their own table so you can do online index maintenance on the main table

    3) use service broker or some other asynchronous mechanism to handle activity against the table

    4) have the service stopped and restarted as part of the index maintenance job

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • October 20, 2010 at 9:39 am

    #1239778

    Wow Thank you SSCRAZY!

    you know your stuff.

    Thanks a lot.

    I will opt for the last solution.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply