This article was previously published under Q251333
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 18971 (SQLBUG_65)
A DUMP DATABASE operation may encounter an undetected deadlock with a user committing a transaction. The user transaction must have performed a modification that would result in a change to sysindexes, such as changing the rows or dpages columns. This deadlock condition leads to the dump process logging flush_cache messages to the errorlog. Neither the DUMP command nor the user transaction will complete, and any subsequent attempts to perform a modification in that database are blocked. The error that occurs is:
flush_cache: timeout, data-in-motion, bp 0x13281a0, pg 0x1a, stat 0x126c/0x264000, obj 0x2, bpss 0)
Specifically, the flush_cache message contains the obj 0x2 and bpss 0 when in this condition.
Other connections attempting to make any modification in the specified database will go to sleep waiting on the dump.
The blocked column of sysprocesses will not show blocking but the waittype of the dump spid remains 0x0022.
Ensure that the dump database command is issued when transaction activity is minimized, preferably idle, in the database.
Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in the Post Service Pack 5a Update for Microsoft SQL Server version 6.5. To install the Post Service Pack 5a Update, you must have either SQL Server 6.5 SP5 or SP5a installed.
For information about how to download and install the SQL Server 6.5 Service Pack 5a, refer to the following article in the Microsoft Knowledge Base:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
If you already have SQL Server 6.5 SP5 or SP5a installed, you can download the Post SP5a Update from the following article in the Microsoft Knowledge Base:
274036 INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update
For more information, contact your primary support provider.
This condition is specifically related to making changes to sysindexes during the commit transaction operation while the dump database is building the appropriate input/output (IO) list of cached buffers for the database. Avoidance of the commit transaction activities performing sysindexes changes is key.
The dump takes an exclusive table lock on syslogs and then tries to checkpoint the buffers for the database. However, the changes flushed to sysindexes on a commit in conjunction with the dump results in an undetected deadlock situation between the dump and the commit.