May 9, 2008 at 2:59 pm
I've read that DBCC REINDEX is an offline operation, because while the REINDEX is in progress, the underlying table is locked.
I would like to set up a DBCC REINDEX script to run at off-peak times. I would like to have the application still available to users while this is running. (I know that performance will be hampered, but that's acceptable as a very small number of users (if any) will be using the system during this period). I would like to make this an automated job, and don't want to have to log in to manually kill the application services before running the script.
Do I risk corrupting the database if I run this while the database is online? Or is it just recommended to be run offline because the table of the index being updated is locked?
May 9, 2008 at 5:13 pm
If you are using the Enterprise edition of SQL2005, you can do online rebuilds with
ALTER INDEX [index] ON
REBUILD WITH (ONLINE = ON)
There is no danger of corruption using this process.
May 10, 2008 at 3:26 am
Not all indexes can be rebuilt online. Clustered indexes on tables with LOB columns cannot be rebuilt online. Non clustered indexes with LOB columns in the index definition cannot be rebuilt online either.
http://msdn.microsoft.com/en-us/library/ms188388.aspx
Ola Hallengren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply