Rebuilding the Management Studio Index in SQL Server 2005

Question

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

Note: I don't mean Maintenance Plans – Dina Dec 19 '11 at 18:23 — Dina, Dec 19 '11 at 18:23

score 2 · Answer 1 · answered Dec 19 '11 at 19:10

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

answered Dec 19 '11 at 19:10

Oleg Dok

261
2
5

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
1

You always can script the action from the properties window and change ONLINE=OFF to ONLINE=ON manually – Oleg Dok Dec 20 '11 at 05:19

score 1 · Accepted Answer · answered Dec 19 '11 at 19:44

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.

answered Dec 19 '11 at 19:44

mrdenny

27,174
4
41
69

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

score 0 · Answer 3 · answered Dec 19 '11 at 19:15

0

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

answered Dec 19 '11 at 19:15

jl.

1,076
8
10

Rebuilding the Management Studio Index in SQL Server 2005

3 Answers3