Microsoft KB Archive/284938

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

Article ID: 284938

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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 &quot;More Information&quot; 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 &quot;ghosted&quot; 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 &quot;S&quot; - Requested by: SPID 10 ECID 0 Mode &quot;X&quot;

== Lock: KEY: 1:30:1 (dd00965bc1b6)

Database: master

Table: sysdatabases

Index: sysdatabases

- Held by: SPID 10 ECID 0 Mode &quot;X&quot;

- Requested by: SPID 6 ECID 0 Mode &quot;S&quot;

Your transaction (process ID #6) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.

Keywords: kbbug kbpending KB284938

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.