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.
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.