Microsoft KB Archive/184829

= PRB: DUMP TRAN Cannot Complete While DBCC NEWALLOC Is Running =

Article ID: 184829

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q184829



SYMPTOMS
If a DUMP TRANSACTION statement is issued while a DBCC NEWALLOC statement is running, the DUMP TRANSACTION statement will go to sleep until after the DBCC NEWALLOC statement has completed.

If you review the syslocks table, you will see that the DUMP TRANSACTION process acquired an exclusive lock on the syslogs table. Additionally, the DUMP TRANSACTION process will not be blocked by the DBCC NEWALLOC, which will not have any locks, either acquired or intended, on the syslogs table.

The status for the DUMP TRANSACTION process, from either a sp_who stored procedure or sysprocesses, will be reported as "sleeping." The waittype will be 0x0.



CAUSE
Checkpointing is blocked in the database against which DBCC NEWALLOC is being run; therefore, the DUMP TRANSACTION cannot issue its final checkpoint until checkpoints can once again be issued (upon the DBCC NEWALLOC statement's completion or termination).



WORKAROUND
The primary problem that can arise as a result of this behavior is that the transaction log may fill up and run out of space while the DBCC NEWALLOC statement is running. To minimize this risk, issue a DUMP TRANSACTION immediately before the DBCC NEWALLOC statement is started, and ensure that the log is large enough to last through any transactions that may occur while the DBCC NEWALLOC statement is running.

Furthermore, it is always a good idea to reduce the amount of activity occurring in a database while DBCC NEWALLOC is being executed. Doing so helps to reduce the risk of spurious errors being reported in the DBCC results.

Additional query words: tran trans

Keywords: kbprb KB184829

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.