February 10, 2010 at 4:02 pm
I have a table that has 236Meg 'reserved' as reported by sp_spaceused.
The transaction log for the db is 1.9G.
If I do a begin tran, delete * from table, the log file goes to about 80% used.
It is part of a 3rd party upgrade script, and the other deletes filled up the tran log.
It is on a test system, so no worries, but I am confused.
If the table is 236M and I delete it, why does it appear to write over a gig in the log file?
Can anyone confirm similar operation?
(the db is in simple recovery mode)
Thanks
February 10, 2010 at 11:16 pm
This was removed by the editor as SPAM
February 11, 2010 at 8:33 am
to solve the problem maybe change the delete *'s to TRUNCATE TABLE
which only logs page deallocations and not each page's changes.
Craig Outcalt
February 11, 2010 at 9:09 am
Ha - that tag line about space couldn't be more wrong.
I did a quick check.
deleting the rows from the table in sql 2000 used 852M log space
deleting the rows from the table in sql 2008 used 1518M log space
So now I need to ask my boss for more disks because the 'new, improved' SQL uses twice the log space?
Can anyone else run a similar test?
Could I have something configured wrong?
Thanks
February 12, 2010 at 7:43 pm
I've seen the same exact behavior migrating from SS 2000 to SS 2005. Of course, the recovery method has a impact, but even with the lowest level (was simple in SS 2000) - has the same problem. In my experience, indexes appear to exacerbate this behavior/problem.
I support 2 (purchased) Vendor apps - that recently migrated from SS 2000 to SS 2005. Both vendors on support phone calls were 'dealing' with this in the same basic manner. We've seen the same thing, other customers are complaining about the same thing, and we don't have any solution beyond buy more disk space.
If any magic bullet exists, I'd sure like to see find it.
On a 2TB Database that I support, I've switched a number of the larger tables over to partitioned tables.
That has allowed us to alleviate a large portion of the problem - but of course, a substantial amount of
additional coding / code modifications were required as well to support the partition swapping/handling.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply