I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner?
Thanks in advance..
As I understand it, when you run a query, the record on disk is simply flagged as deleted, and the Ghost Cleanup process does the actual deletes. If you are already on the latest service pack, then you would want to look at how often your data is deleted/refreshed or updated.
EDIT: Oh, as such, the Ghost Celaner can not be disabled, or else your database would simply bloat out of proportion.
We actually had a great thread on the subject of "ghost records" and the cleanup process a few years ago... We had a number of posts from Paul Randall, the GPM responsible for the Microsoft SQL Server physical storage engine that covered the topic in some detail. Unfortunately that was a bit of DBForums history that was lost in a server melt-down.
The actual thread is called the "Ghost Exorcisor" and it runs as a system task (higher CPU priority than a user task) according to rules that are fairly simple, but beyond user control. There is a trace flag that you can set to prevent the Ghost Exorciser from running, but this is a VERY bad idea in most cases.
If you want to pursue this issue, I would suggest that you contact Microsoft Professional Support Services. They will want a bunch of information from you, will probably not charge you for the call, and will probably have a lot of good ideas that will help you more than just turning the exorciser off.