Sql 2005 64-bit Enterprise on Sql 2008 R2 64GB memory
I've never seen this issue on any of our other test or production boxes. Quest Spotlight clearly showed checkpoint on the user database being blocked indefinitely by ghost cleanup. This test box is a little faster than our other sql servers as far as clock speed, but production has lots more memory and similar "16 cpu" setup.
I noticed on one of our test sql boxes that the ghost cleanup seemed to be running constantly and blocking checkpoint on the 1.7TB copy of production there. I don't believe any significant number of deletes were being done in testing. Also had the error messages below showing in the log. After a lot of research I decided to turn off ghost cleanup since I planned to do a full reindex anyway.
dbcc traceon (661,-1)
A time-out occurred while waiting for buffer latch -- type 3, bp 0000000152FE2980, page 11:4240586, stat 0x5c00009, database id: 6, allocation unit Id: 72057595261550592, task 0x00000000092125C8 : 0, waittime 300, flags 0x3a, owning task 0x000000000423A868. Not continuing to wait.
Error: 845, Severity: 17, State: 1.
On big systems it's possible for the ghost cleanup process to fall behind the rest of the system, with no hope of it catching up. It's a single-threaded task - so imagine a 16-way box with lots of deletes occuring, and a single CPU spending a few seconds every 5 seconds trying to remove all the ghosted records resulting from the deletes of all the other CPUs. It's pretty obvious that the ghost cleanup process is going to lag behind.
If you disable the ghost cleanup task, the space taken up by deleted records will *NOT* be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.