Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Does anyone know if using the SQL Server Management Studio Index Rebuild, rebuilds the index online or offline?

Reading the help documentation doesn't say anything on this (so I would assume it is offline) but I would like to know, as I prefer using the GUI over Transact SQL.

If the rebuild happens online, then how would I know how long it takes? The Index Rebuild window closes after a few seconds, so is it fair to assume that at this point the rebuild is complete, or is it still going on in the background? There's no indicator of any change

share|improve this question
    
Note: I don't mean Maintenance Plans –  Dina Dec 19 '11 at 18:23

3 Answers 3

up vote 1 down vote accepted

It's offline unless you use the options tab to make it an online operation (if that's supported on your edition).

If the Window closes then the operation is complete.

share|improve this answer
    
Using SSMS 2008 R2 with SQL Server 2005 Enterprise (Which is okay for Online Rebuilds) but no such option for SSMS.. atleast not that I've discovered yet ;) –  Dina Dec 19 '11 at 20:50
    
On the options page, where you check the box to rebuild, there's a check box that is grayed out which says "Allow online processing on DML statements while creating the index". It appears to only be available when you want to change the fill factor setting. If you use the DML directly this is an option you can set no matter what settings if any you change. –  mrdenny Dec 19 '11 at 22:11

If you do it thru context menu - it is offline If thro index's properties window - then the behavior is controlled by checkbox "Allow online processing..." on the Options tab

share|improve this answer
    
Hmmm.. Using SQL 2005 Enterprise, and no "Allow online processing.." checkbox under Index Properties. Unless I check off "set Fill Factor" options - which gives me "Allow online processing of DML statements.." –  Dina Dec 19 '11 at 20:48
    
You always can script the action from the properties window and change ONLINE=OFF to ONLINE=ON manually –  Oleg Dok Dec 20 '11 at 5:19

If you run Profiler why using the SSMS Index Rebuid you'll see ONLINE = OFF.

share|improve this answer
    
So cool, thanks for the insight! –  Dina Dec 19 '11 at 20:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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