index rebuild offline manage db downtime

Question

index rebuild offline manage db downtime

tina 2565

Old Hand

Points: 349
More actions
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?

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

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

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."

tina 2565 Old Hand Points: 349 More actions · Answer 3

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?

Geoff A SSChampion Points: 11511 More actions · Answer 4

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.

tina 2565 Old Hand Points: 349 More actions · Answer 5

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?

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

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]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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

tina 2565 Old Hand Points: 349 More actions · Answer 8

Wow Thank you SSCRAZY!

you know your stuff.

Thanks a lot.

I will opt for the last solution.