SQL 2000 Index Locking

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert - 02 Jul 2008 14:36 GMT

I'm trying to create a new covering index on a table with ~1.1M rows. I know
SQL 2005 has some nice online index creation features, but is there any way
to avoid the huge table lock during creation in SQL 2000?

I tried to create it this morning when usage was low, but it was still
running after ~25 min and I had to kill it.

Thanks

Reply to this Message

Russell Fields - 02 Jul 2008 15:02 GMT

Robert,

No, there is not. This is called online indexing and was added with SQL
Server 2005, but only as noted from the BOL: "Online index operations are
available only in SQL Server 2005 Enterprise Edition."

RLF

> I'm trying to create a new covering index on a table with ~1.1M rows. I
> know
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message

TheSQLGuru - 02 Jul 2008 16:17 GMT

As Russell mentioned there is no way to avoid the lock. However, did you
size the database with enough empty space such that there won't be any
autogrowths during the index create? that could have a negative effect on
the duration, especially if you still have the default growth increment of
1MB (which would be very unfortunate, btw).

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> I'm trying to create a new covering index on a table with ~1.1M rows. I
> know
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message