Prev: ORDER BY in derived table for update
Next: LongText not supported as output parameter
From: RJ Roberts on 31 Mar 2010 18:47
A possible explaination:

You don't mention what version of SS 2008 you are using. I'm almost certain
you need the Enterprise version to use the Online Index rebuild option. What
I have seen in the past however is that it doesn't complain about the Online
= On with a non Enterprise version it just goes ahead with an offline
rebuild. Which would explain your difficulties.


--
RJ Roberts
DB Architect/Developer


"DB" wrote:

> I use the following statement to rebuild indexes on my highly transactional
> SQL 2008 database. There is no downtime for this database as we are a
> hospital and it stays busy 24/7. So I thought I'd use the ONLINE = ON
> function and SORT_IN_TEMPDB = ON function as follows:
>
> ALTER INDEX MyIndex ON MyTable REBUILD PARTITION = ALL WITH (ONLINE = ON,
> SORT_IN_TEMPDB = ON)
>
> I have over 200 indexes that are over 75% fragmented. So I put them in a
> loop using dynamic sql. After each rebuild, I use the WAITFOR command for 5
> min. to allow the server to "recover" from the previous rebuild. After about
> 5 or 6 rebuilds, major blocking occurs and I have to kill my script. Using
> ONLINE = ON is NOT supposed to cause blocking. What is going on? I need to
> rebuild these indexes, but can't figure out how to do it without bringing our
> system apps to their knees due to blocking.
>
> Thanks.
>
> --
> DB
 | 
Pages: 1
Prev: ORDER BY in derived table for update
Next: LongText not supported as output parameter