"Ghost Cleanup"? Configuration?
-
Strange... We've just had what felt like a five minute break in the system when a process called "GHOST CLEANUP" locked up a couple of tables.
This is a new one on me, but it appears as though SQL Server 2000 doesn't delete extents immediately, but leaves a daemon running to sort it out later. The question I have is: How can I stop it from locking stuff up just when the rest of the system is at its busiest?
Thomas Rushton
blog: https://thelonedba.wordpress.com -
Is the database set for autoshrink?
Cheers,
- Mark
Cheers,
- Mark -
quote:
Is the database set for autoshrink?No. I've just checked.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
So much for that theory.
How about a Maintenance Plan job (with "remove unused space") maybe scheduled for the wrong time of day. Or maybe some other scheduled shrink?
Cheers,
- Mark
Cheers,
- Mark -
quote:
So much for that theory.Sorry. Nice try, though!
quote:
How about a Maintenance Plan job (with "remove unused space") maybe scheduled for the wrong time of day. Or maybe some other scheduled shrink?A refinement of the same theory? Nice try, but nothing is trying to shrink the database.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
To confirm your observations, found this on TechNet
quote:
When you delete rows, pages, or extents 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 cleanup." Ghost cleanup improves the performance of the DELETE command because SQL Server doesn't have to deal with physical cleanup right away.No solution though. Is it possible that someone did a lot of deletes, the server has a lot or cleaning up to do or the server was busier than usual.
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
To confirm your observations, found this on TechNetI saw that too, before I posted here. It wasn't particularly helpful, so I thought I'd turn to the gurus...
quote:
No solution though. Is it possible that someone did a lot of deletes, the server has a lot or cleaning up to do or the server was busier than usual.The locks were against a particular table; the only process that deletes from that table is a scheduled job that runs once a day at 5am. Quite why the ghost process waited for 8-9 hours or so before cleaning up after itself, I don't know... Oh, and the process only deletes about 15-20k rows.
Couldn't find anything else relevant on MS's website either.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
Back to barking up the "shrink" tree
.... maybe an explicit DBCC shrinkfile/shrinkdb after the 5am deletions will force the background ghost cleanup to do its dirty work immediately.Cheers,
- Mark
Cheers,
- Mark -
If it's something that would happen in slack time, there's plenty of that around at that ungodly hour of the morning! We have a few scheduled jobs that take about two minutes in total to run; the next thing that happens is the first (transaction) dump of the day, which is a biggie (containing all the changes created by optimization jobs...) Apart from that, the system is fairly quiet until about 8ish.
This whole thing seems to be shrouded in secrecy. Apart from that one technet article, and a few bits that make oblique reference to ghost cleanup, there's precious little out there. Why is the Microsoft SQL Server development team not responding? Where are you, guys? Other SQL Server product companies read these pages! c'mon!
Thomas Rushton
blog: https://thelonedba.wordpress.com -
quote:
Why is the Microsoft SQL Server development team not responding? Where are you, guys? Other SQL Server product companies read these pages! c'mon!They cannot follow everything. Microsoft does follow the Microsoft.public.sqlserver.* Usenet groups and promises to give an answer if nobody beats them to it.
-
You can find information about ghost cleanup on sql server tips and tricks section.
When you delete a record from any table in SQL Server, SQL Server doesn't actually delete these records. SQL Server marked them as ghost record. The purpose is to increase delete time. These records will be deleted by a batch process called GHOST CLEANUP PROCESS.
Hendra
-
Hi, we experience same problem and we try to reindex tables every day in order to solve it or at least ghost cleanup wont start in hours 9 to 17. It must be though a firing condition for the process to start . Also there should be a way to stop this process even manually if it starts during busy time. These are not documentd anywhere and only development of Microsoft might tell us few words on this one. Anyone knows how to access these guys?
-
There is a known issue(bug) with the checkpoint handler and ghost cleanup not playing nice with each other. If you are performing heavy data updates, more specifically delete operations, you may have long pauses where everything seems to stop, but no blocking is occuring. We experienced this issue while benchmarking on a Unisys ES7000 and EMC CX700. The system would scream for about 1-2 minutes, then there would be a 30 second to 1 minute pause. During the pause, all system activity would almost stop. Each time the system paused, the GHOST CLEANUP process would be running for one of the databases, as well as CHECKPOINT. I was referred to the following KB article by MS support: http://support.microsoft.com/default.aspx?scid=kb;EN-US;815056
There may be a patch for this, but you might have to put in a support call to see if this is truely the problem on your system.
Another KB of possible interest is this: http://support.microsoft.com/default.aspx?kbid=810885
-Dan
.... maybe an explicit DBCC shrinkfile/shrinkdb after the 5am deletions will force the background ghost cleanup to do its dirty work immediately.Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply