DBCC INDEX DEFRAG being blocked.

Question

DBCC INDEX DEFRAG being blocked.

Phil Johnson

SSC Enthusiast

Points: 148
More actions
October 25, 2005 at 10:27 am

#110635

Hi,
I am currently trying to run dbcc index defrag for all user tables on my database while a small number of users (10) are on the system. I am using SQL 2000 sp3a
However each time I run the command it seems to be blocked by a query which is coming from my application. The query is accessing a user table and should execute in < 1 second. However enterprise manger and sp_who2 report that the process running the query blocks the process running dbcc index defrag for up to an hour.
When I manualy kill the query process, the dbcc process is then blocked by a new process running the same query. This continues untill I log all the users off the system. At that point the dbcc job runs to completion normally.
Has anyone else seen similar problems? Checking book online and other threads on dbcc index defrag this seems to be the correct job to run while users are using the system.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kory Becker Hall of Fame Points: 3585 More actions · Answer 1

DBCC INDEX DEFRAG is going to want to do some type of exclusive locking more than likely at a page level. If the application holds an in-compatible lock the DBCC command will have to wait until it can get a lock to do its work.

For best practices do all maintenance in the off hours assuming you have such a window of opportunity

Phil Johnson SSC Enthusiast Points: 148 More actions · Answer 2

Thanks and agreed unfortunately I don't have such a window (hence the test to see what the effect of running dbcc indexdefrag is when users are on the system)

It still does not explain why the query does not release the lock and dbcc indexdefrag is blocked untill all users are logged off.

Kory Becker Hall of Fame Points: 3585 More actions · Answer 3

Is it always the same object that has locks that block the DBCC command? It could be a central table to the application and the isolation level of the sql called from the application is the problem (login or settings table). Run sp_lock and get an example of what type of locking (PAG, TAB) is taking place and where (spid, db, object, type, mode) and resolve the db and object using db_name(id), object_name(id). Run sp_lock numerous times to get a feel for how often, and what objects have locks. Run the DBCC command and try to maybe isolate where the contention is.

Phil Johnson SSC Enthusiast Points: 148 More actions · Answer 4

The contention seems to alway be on the same query and table. If the query is key to the application which I am assuming that it is then how could I stop it blocking the DBCC

I'll run the test again tomorrow and try and get the stats. Will update you with the results.

Phil Johnson SSC Enthusiast Points: 148 More actions · Answer 5

Hi Have doen some digging and the dbcc indexdefrag process (spid 60) is being blocked a process (399) which is running a simple select statement from a user table. sp_who2 reports that there is nothing blocking the process 399, however it does not seem to release its lock,

The results of sp_lock 60, 399 are shown below.

SPID DBID OBjID indid type resource mode status

60 7 0 0 DB S GRANT

399 7 0 0 DB S GRANT

60 7 0 0 EXT 1:334504 X GRANT

60 7 0 0 PAG 1:334504 X GRANT

399 7 197575742 1 PAG 1:7120709 IS GRANT

60 7 197575742 1 PAG 1:7120709 X WAIT

60 7 0 0 IDX IDX: 7:197575742 X GRANT

399 7 197575742 0 TAB IS GRANT

60 7 197575742 0 TAB IX GRANT

I have run the query being executed in 399 seperately and this returns in < 1 second. So I don't understand why the IS lock on 1:7120709 has no been released. Could connection pooling be causing this?

Kory Becker Hall of Fame Points: 3585 More actions · Answer 6

Obviously these locks are not copmaptible! Connection pooling should leave shared locks at the DB level when the process is done with the connection. What is the application doing is the question now. Is the application destoying or closing the connection when it is done? Is this a vendor or in-house application?

Phil Johnson SSC Enthusiast Points: 148 More actions · Answer 7

Its in house. I have put the question back to the dev team to find out what the application does with the connection.

I am also looking into the possibility of adding the nolock hint to the select statement.

Kory Becker Hall of Fame Points: 3585 More actions · Answer 8

I hope you find that the developers are openeing a connection, executing a query and then not doing .close on the object.

Phil Johnson SSC Enthusiast Points: 148 More actions · Answer 9

This was exactly what happened for any who is interested. Connection not being closed correctly by development