question

smayer avatar image
smayer 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.
sql-server-2012tableshrink
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
1 Like 1 ·
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.
0 Likes 0 ·
ruancra avatar image
ruancra answered
Have you checked the size of your log file? What Recovery Model is the database set to?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Full recovery; I take log backups every 15 minutes and have procedures set up to shrink the log if it exceeds a certain size.
0 Likes 0 ·
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
0 Likes 0 ·
You can also run sp_spaceused 'tablename' on a particular table to check space used and available.
0 Likes 0 ·
eric_russell avatar image
eric_russell answered
To recover free space allocated to deleted rows in a table, you must rebuild the clustered index.
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What does sp_spacedused show for this table?
0 Likes 0 ·
Rebuilding the clustered index does not work; that's the first thing I tried.
0 Likes 0 ·
name rows reserved data index_size unused 264 5331784 KB 5330424 KB 880 KB 480 KB
0 Likes 0 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Thanks, but no dice. Table size and free space unchanged. I tested on a 24MB table in our test environment that has one row of data; recreating that table with the same indexes is a mere 32 KB.
0 Likes 0 ·
Show more comments
Cyborg avatar image
Cyborg answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pipthegeek avatar image
pipthegeek answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Possibly; I think the problem is there are other connections open which are not releasing the snapshot data -- but thought this was only kept in tempDB ...
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.