GHOST CLEANUP

Hi All,

I am using SQL Server 2000 sp3 on Win NT 5.0 (2195) + 2 CPU

I continuously have a background process number SPID 5, that is a GHOST CLEANUP. The database that this process shows to work in context is absolutely inactive. Even when I stop and restart SQL Server this process shows up again. I noticed that this process might affect performance on the server. The last major change to the database was a long time ago, so I can't see any reason why ghost do records still exist and why does this process runs.
What could be the reason for that and how can I stop this process to run.
To mention that in other server which I have the production copy of the database in context and it is much more active, I see nothing of this GHOST CLEANUP process.

Any help ??

Thanks in advance.


Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

When you delete data in your database, SQL Server can mark those objects as "ghosts" (meaning that deletion is pending) and clean them up later by using a background task. This process is called Ghost Record Cleanup. Ghost Record Cleanup improves the performance of the DELETE command because SQL Server doesn't have to deal with the physical cleanup right away.


Looks like in your database lot of delete statement is happening frequently.

-Nilay

Thankd guys for that. The problem is that I see this task runing even if there is no DELETE hapening or was hapening for a long time (2 weeks) before. Even after I restatred the server it rasies up and start.

Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

"SQL Server provides a special housekeeping thread that periodically checks B-trees for ghosted records and asynchronously removes them from the leaf level of the index. This same thread carries out the automatic shrinking of databases if you have that option set.

In the leaf level of an index, when rows are deleted, they're marked as ghost records. This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost. The page header also reflects the number of ghost records on a page. Ghost records are primarily a concurrency optimization for key-range locking. "
(from Inside SQL Server 2000)

I think this Thread keeps on running ALL THE TIMES to check for any deleted rows/shrinkdatabase.

HTH
Harsh