asked
Table Size Won't Shrink After Deleting Data. Why?
Hello, I have a several GB table that refuses to reduce its size, even after most of the data is deleted -- Copying the data to another table reveals the remaining indexes and data are less than 100 MB. Application and replication limitations make it difficult to truncate the table or migrate to a new replacement object. Instead, I need to apply a selective delete statement, leaving me with the bloated table problem. Shrinking DB files or the DB itself has no effect on the table size. We are required to have "allow snapshot isolation" and "is snapshot read committed on" database options set to true. We are running SQL Server 2012 with the database in 2005 compatibility mode, on a Windows 2008 R2 Windows server. Has anyone come across and resolved this specific issue? Note we believe the problem is specifically due to the "allow snapshot isolation" and "is snapshot read committed on" options set to true. Can anyone think of a workaround that takes into account these settings -- suggested answers haven't worked so far, as of 4/17 3PM PDT.
What's the schema for the table (including indexes/keys)? Any "off-page" data? How many rows in the table before you started deleting, and afterwards? And how are you measuring the size of the table?
If any of the answers below is helpful, please indicate that by clicking on the thumbs up next to each helpful answer. If any of the answers solved your problem, indicate that by clicking on the checkbox next to that answer.
Have you checked the size of your log file? What Recovery Model is the database set to?
ok, run this query to determine how much space is free and allocated to the data/log files: SELECT Name ,Filename, CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)], CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)], CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)] FROM dbo.sysfiles a (NOLOCK) If you notice that there are Available free space in a file do a DBCC SHRINKFILE (2,100) -- Adjust the parameters accordingly
To recover free space allocated to deleted rows in a table, you must rebuild the clustered index.
So there are 264 rows but 5,331,784 KB reserved. Drop indexes, aler table and drop clustered key, then re-create indexes. That should do it. However, don't shrink the database again.
Just to clarify, do the following in proper order: - Drop all non-clustered indexes (do this first, because they are dependent on clustered key) - Drop clustered index - Re-add same clustered index above (this should re-organize all rows in the table) - Re-create indexes (this should re-oganize all rows in indexes using new keys) That should do it.
Is that a heap where you are doing the delete? The pages made emptied using delete on a heap will not be allocated or released to other objects even after shrinking, those pages are still allocated to its heaps. So create a clustered index then drop the clustered index if not needed. Other method is to DELETE with TABLELOCK hint, this will deallocate the pages.
Are there other transactions running that are reading data? Snapshot isolation means that SQL must keep copies of any changed or deleted data if it might be required by other transactions. Once the other transactions have finished it should eventually free up the space it is using.
I thought it was all kept in tempdb but I don't know for sure. My guess is that the records are being marked as ghost in the table but staying until existing transactions have finsihed with them. Sadly I don't have any evidence for this other than it fits with the behaviour you have observed so far.
19 People are following this question.
