FIX: Dump Database Logs flush_cache Errors and Does Not Complete (251333)
The information in this article applies to:
This article was previously published under Q251333 BUG #: 18971 (SQLBUG_65) SYMPTOMS 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. WORKAROUND Ensure that the dump database command is issued when
transaction activity is minimized, preferably idle, in the database.
STATUS 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.
MORE INFORMATION 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.
| Modification Type: | Major | Last Reviewed: | 9/6/2006 |
|---|
| Keywords: | kbBug kbfix KB251333 |
|---|
|