Problems with ghost cleanup system process

  • aslorenzo

    Old Hand

    Points: 343

    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

  • GSquared

    SSC Guru

    Points: 260824

    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: 1004446

    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

    Old Hand

    Points: 343

    Thanks a lot.

    Lorenzo Ascione

  • GSquared

    SSC Guru

    Points: 260824

    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: 1004446

    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

    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

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

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