Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC REINDEX - Offline? Expand / Collapse
Author
Message
Posted Friday, May 9, 2008 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 8, 2011 2:42 PM
Points: 9, Visits: 186
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?
Post #498246
Posted Friday, May 9, 2008 5:13 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:15 PM
Points: 750, Visits: 3,157
If you are using the Enterprise edition of SQL2005, you can do online rebuilds with

ALTER INDEX [index] ON [table] REBUILD WITH (ONLINE = ON)

There is no danger of corruption using this process.
Post #498276
Posted Saturday, May 10, 2008 3:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 168, Visits: 1,860
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
http://ola.hallengren.com
Post #498338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse