BUG: Dump Database Logs flush_cache Errors and Does Not Complete

ID: Q251333

The information in this article applies to:

Microsoft SQL Server version 6.5

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.

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.

Additional query words:

Keywords : kbSQLServ650bug
Version : winnt:6.5
Platform : winnt
Issue type : kbbug