1

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

1
  • Note: I don't mean Maintenance Plans
    – Dina
    Dec 19, 2011 at 18:23

3 Answers 3

1

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.

2
  • 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, 2011 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, 2011 at 22:11
2

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

2
  • 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, 2011 at 20:48
  • 1
    You always can script the action from the properties window and change ONLINE=OFF to ONLINE=ON manually
    – Oleg Dok
    Dec 20, 2011 at 5:19
0

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

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .