PRB: DUMP TRAN Cannot Complete While DBCC NEWALLOC Is Running

• Microsoft SQL Server version 6.5

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 : SSrvTran_SQL
Version : WINNT:6.5
Platform : winnt
Issue type : kbprb