DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
--
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC dllname (FREE)

According to SQL Server 2000 Books Online, this command requires membership
in the sysadmin or db_owner role. We have added the user to the db_owner
role of database 'master', yet the command fails and reports error 2571. Is
this the correct database? (The command succeeds so long as the login is a
member of sysadmin.)
Thanks,
DanaHi Dana,
My understanding of your issue is that:
You found that DBCC dllname(FREE) could not be executed with db_owner. You
could run it with sysadmin.
If I have misunderstood, please let me know.
I looked through our internal database and found that this was a
documentation issue ( 223167 ) of BOL.
BOL should read:
DBCC dllname (FREE) permissions default to members of the sysadmin fixed
server role, and are not transferable.
Please assign the login account with sysadmin permission to run the
statement.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Dana,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support