BUG: Ghosted Record Cleanup Process May Go Into CPU Spin Due to an Invalid Check for Cleanup Type (284938)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q284938
BUG #: 101080 (SQLBUG_70)

SYMPTOMS

Under certain conditions, it is possible for the ghosted record cleanup process to go into a CPU spin where the only recourse is to cycle the computer running SQL Server.

In a ghosted record cleanup process (usually server system process id [SPID] 6), symptoms include the following:

  • Slow response by the computer running SQL Server.

  • Maintains high CPU usage indefinitely.

  • Sysprocesses.cmd may change from AWAITING CMD to DBCC.

  • Process is not killable.

  • Might deadlock with log restore process. Refer to the "More Information" section of this article for deadlock symptoms.

CAUSE

If the background cleanup process encounters a lock request time-out while processing a cleanup task, the cleanup process reschedules the cleanup task for another time with incorrect information. The next time the cleanup process attempts to carry out the task, it may result in a CPU spin due to the incorrect information associated with the task.

WORKAROUND

Schedule the process that was contending with the background cleanup task to run at a time when data modification activity is low.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

The ghosted recorded cleanup process is a special SQL Server housekeeping background process that periodically checks for existing ghosted records and asynchronously removes them. To improve concurrency optimization, index leaf records that are to be deleted are initially marked as "ghosted" to be physically deleted later by this process.

The ghosted recorded cleanup process normally is indicated as SPID 6. The thread associated with this process is also responsible for automatically shrinking a database if the AUTOSHRINK database option is enabled.

An example follows of the deadlock symptoms (using trace flag 1204) that you encountered. The deadlock is between the cleanup process (SPID 6) and a log restore process (spid 10).
*** Deadlock Detected ***
==> Process 6 chosen as deadlock victim
== Deadlock Detected at: 2000-11-28 00:51:05.58
== Session participant information:
SPID: 6 ECID: 0 Statement Type: DBCC Line #:0
SPID: 10 ECID: 0 Statement Type: RESTORE LOG Line #: 1
Input Buf: restore log [HSB] from DISK=...

== Deadlock Lock participant information:
== Lock: KEY: 1:30:2 (e200382bc12a)
Database: master
Table: sysdatabases
Index: ncsysdatabases
- Held by: SPID 6 ECID 0 Mode "S" - Requested by: SPID 10 ECID 0 Mode "X"

== Lock: KEY: 1:30:1 (dd00965bc1b6)
Database: master
Table: sysdatabases
Index: sysdatabases
- Held by: SPID 10 ECID 0 Mode "X"
- Requested by: SPID 6 ECID 0 Mode "S"

Your transaction (process ID #6) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.
Modification Type:MajorLast Reviewed:10/16/2002
Keywords:kbBug kbDSupport KB284938
©2002 Microsoft Corporation. All rights reserved.