Hi,
Got a 2TB ss2014 DB and applied Clustered Column Store Indexes (compressed) which brings the data size down to 350GB. Understanding head room + 100% additional space is required for INDEX REBUILDS, there is still vast amount of unused space.
Primary data file initial size was 1TB, now 900GB empty and usual DBCC SHRINKFILE(, 512000) is not working. DB is in simple recovery mode and on Fusion-IO storage.
Any ideas why I cannot shrink data file below initial size? Thanks
My recollection on this may be very out of date, but might a CHECKPOINT be required before the Shrink can do its stuff? (although that might be the Log File rather than the Data File)
Thanks Kristen.
Good point and had not completed a CHECKPOINT. Will test and update this post
DBCC SHRINKFILE does what the name implies. It shrinks the size of a database file, either to a specific size or as small as it can. It does this by moving pages from the back of the file to the first free page in the front of the file with the consequence that pages end up in reverse order. For more read this SQL Server Log Explorer blog
hope this help you
The linked article describes shrinking the log file, not the data file, and I can't see that it has any relevance. The title is "SHRINKING THE TRANSACTION LOG IN SQL SERVER WITH DBCC SHRINKFILE" and it makes no mention of shrinking the data file but yet has this conclusion:
"The DBCC SHRINKFILE operation is mainly useful for a truncate table or a drop table operation because these operations create plenty of unused space. Most of the databases need some free space to perform normal day-to-day operations. Shrinking the Transaction log in SQL Server with DBCC Shrinkfile helps you to utilize the unused space on SQL Server."
which has everything to do with shrinking a Data File (as said, that is not mentioned in the article) and almost nothing to do with shrinking a log file.
Perhaps I missed something? but it seems like a "fluff" article to me, rather than one of substance.