question

Felipe avatar image
Felipe asked

DBCC DBREINDEX WITHOUT LOCK CONNECTIONS

Friends,

I need to do a DBCC DBREINDEX in my database. But when i start the command, the users are locked. In my environment, the people access the database during the day.

Is There a way to reindex the database without locking the users?

sql-server-2000maintenancefragmentation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

There is no way to reindex a table without creating locks on that table. Generally, I would suggest doing this only during a planned maintenance window if possible for precisely this reason.

If you must do it while the object is in use and you cannot afford to wait, you can work around it using by using the with (nolock) table hint, or equivalently by changing the transaction isolation level. But you should use that very cautiously as it can in some circumstances return inconsistent results.

Of course, the other question is that if you do not have a maintenance window and must do this during database use, is if it is worth doing at all. A general rule of thumb is to reindix if convenient around 10% fragmentation, but some have argued that it makes since to let it go up to 30% or even higher before worrying about it, especially if the reindex operation itself will cause problems.

Just as a side note, I noted that this was with SQL Server 2000 so DBCC DBREINDEX is the way to go, but in later versions of SQL this is being phased out in favor of Alter Index.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

In later versions of SQL server you are able to build the index "online", but it will place locks in the tables anyway, but it will not disturb others as much as in SQL server 2000.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

Just adding to Hakans comment, online rebuilds are only available in the enterprise and Dev editions.

See note in the "ONLINE=" section

http://msdn.microsoft.com/en-us/library/ms188783.aspx

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
You are absolutely right, I am too spoiled with Enterprice editions! :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.