PRB: DUMP TRAN Cannot Complete While DBCC NEWALLOC Is Running (184829)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbprb KB184829 |
|---|
|