January 13, 2004 at 9:00 am
Hi,
I have a large audit record table used in many databases across multiple sites. These tables typically store anywhere upwards of 16 million rows.
On one particular database on one site I have been removing historic records and cannot seem to see any reduction in the space used when calling the sp_spaceused SP. When I add together the data space and index space then divide by the number of rows, I normally expect each row to use 1.5kb, in this one database it uses 3.05kb. SP_Help on this table shows the same table definition, collation and indices.
I've tried a DBCC UPDATEUSAGE, a DBCC SHRINKDATABASE and a DBCC indexdefrag but none seem to reclaim any space back. The database is also expanding indicating that new rows in the table are not occupying the space of deleted rows.
Does anybody have any idea what is ahppening? I've tried a DBCC CHECKTABLE and there are no errors. I'd rather not attempt to DTS the data out and back into a new table if possible.
January 13, 2004 at 9:17 am
Does the table have a clustered index?
--Jonathan
January 13, 2004 at 9:24 am
Does the table have text/image columns?
Can you post result of sp_spaceused @updateusage = true here?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply