Microsoft KB Archive/251333

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


Article ID: 251333

Article Last Modified on 9/6/2006



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



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.


Keywords: kbbug kbfix KB251333