Problems with ghost cleanup system process

Question

Problems with ghost cleanup system process

aslorenzo

SSC-Addicted

Points: 423
More actions
May 7, 2008 at 6:26 am

#187230

We have a server with sql2005 sp1:
Microsoft SQL Server 2005 - 9.00.2153.00 (X64)
May 9 2006 13:58:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
ghost cleanup system process after a reboot of sql server started to slow down the server.
The process took a lot of resources and is running for hours.
How we can solve ?
I read in some blogs that a solution should be installing the sp2 but since is a production server we cannot do it immediately.
Thanks in advance.
Lorenzo Ascione

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I read that ghost cleanup is part of the autoshrink process. Do you have autoshrink turned on for this database? If so, turning that off should help your performance.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It does work for auto grow and shrink, also cleans up dropped objects or indexes.

Done anything like that recently?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aslorenzo SSC-Addicted Points: 423 More actions · Answer 3

aslorenzo

SSC-Addicted

Points: 423

May 8, 2008 at 1:21 am

#812258

Thanks a lot.

Lorenzo Ascione

GSquared SSC Guru Points: 260824 More actions · Answer 4

GilaMonster (5/7/2008)
It does work for auto grow and shrink, also cleans up dropped objects or indexes.
Done anything like that recently?

Per MSDN, it does all that, but when it's just cleaning stuff up, it only runs for a very short time and is very light on locks. But when it's involved in auto-shrink? Well, we already know that's a huge performance killer.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I've also seen it bring the system to an absolute halt during an auto grow.

You're right on the dropped obejcts. While I've seen Ghost cleanup tidying up after a drop, it doesn't (usually) affect other processes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Per this post from a Microsoftie, there were patches for both SP1 and SP2 related to ghost cleanup:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2397281&SiteId=1

-T661 turns of the ghost cleanup. It is used in TPC configurations. THIS IS PROBABLY not a good idea for production. It is detailed here:

http://support.microsoft.com/default.aspx/kb/920093

Also see here for more info: http://www.sqlskills.com/blogs/paul/2007/10/08/InsideTheStorageEngineGhostCleanupInDepth.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service