May 5, 2011 at 10:50 am
Hi
My project is using sql server 2005.
I have a table which is being inserted and deleted in a huge number of rows (around 200,000rows) by the batch job every 15mins. This table is being used in the application, and the number of rows more or less the same (it's like the mirror of a complicated view query)
The problem is if i query using sp_spaceused, i can see that the table keep growing every time the batch job runs, and eventually reach 2GB size such that hampering the performance of the application.
Seems like the space of the deleted rows never get reclaimed back.
After it reaches that level, i need to truncate the table, or recreate the cluster index, so that the table shrunk and become very small (30MB).
Question is , is the GHOST CLEANUP process in my db not running properly? or is there any other configuration in the server that define the ghost-cleanup configuration?
Btw, this is happening in our UAT server, we have DEV server with the same data, the table keep maintained the size between 20-30MB. I can't find the different configuration of the 2 database, except one is using SIMPLE recovery model, and the DEV is using BULK_LOGGED recovery model.
Any advise need to look at really really appreciated, since im bit frustrated already 🙁
thanks,
edward
June 1, 2011 at 11:32 am
Not sure if this is still relevent any more, but since no one else has posted ...
I've read about situations like this before. Deletes of large numbers of rows can cause the Ghost Cleanup process to lock the table for longer than expected, preventing further inserts for sometimes several minutes. I have a few questions though:
Does your batch job try to insert the new rows immediately after deleting old ones?
Are ALL the old rows deleted prior to the insert?
Is 2Gb REALLY a problem size? It's not really terribly big.
If you want to re-populate the table every time the batch runs, wouldn't it be simpler to drop and recreate it as the first step of the batch? OBviously this will only work if you completely delete the rows between batch cycles, but it would be far and away the easiest solution.
Other people have reported that these Ghost Cleanup problems have improved with SP1 and SP2 for SQL 2005. You don't say which patch you're on?
June 2, 2011 at 3:27 pm
I've seen similar symptoms occur when deleting rows from a heap table (table with no clustered index). This describes the workaround: http://support.microsoft.com/kb/913399
Kevin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply