I have searched the site and found numerous posts on this issue, but none really answer what I am experiencing.
Background
( I know shrinkfile is bad, but in this case, it makes sense)
Our client has an .mdf file that is 1.65 TB in size, 1.26 TB is used, and about 390 GB is available in that file. This data file is not intended to grow much at all, and should only contain the system objects. Unfortunately there are large tables created in that file by mistake totalling about another 800 GB that need to be moved to a different file group in order to take advantage of multiple files in the correct file group and a faster disk subsystem.
I wanted to test the shrinkfile before moving the clustered indexes to a new file group so we didn't end up with 800 GB larger DB if the shrink didn't work, which is exactly what is happening. We are doing this on a new Prod server they will be migrating to shortly, and are using SA credentials with Sysadmin rights.
No matter what commands we run to shrink the file, the command takes about 10-15 seconds to execute it successfully. No errors show up, but the file does not shrink. I have tried the below commands in SSMS, tried to use the GUI shrink file directly, scripting out the GUI commands, and straight T-SQL with no movement on the data file size. Initially I had thought that truncate option was the problem, as their might not have been free space at the end of the file. But even after running the notruncate keyword first, then using truncate, the file still did not shrink.
The following query shows that the database file does indeed have space available in the file:
SELECT [Segment Name] = RTRIM(name) ,AllocatedMB = CAST(size / 128.0 AS DECIMAL(10, 2)) ,UsedMB = CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) ,AvailableMB = CAST(size / 128.0 - (FILEPROPERTY(name, 'SpaceUsed') / 128.0) AS DECIMAL(10, 2)) ,PercentUsed = CAST((CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(10, 2)) / CAST(size / 128.0 AS DECIMAL(10, 2))) * 100 AS DECIMAL(10, 2)) FROM sysfiles ORDER BY groupid DESC
Result:
Segment Name AllocatedMB UsedMB AvailableMB PercentUsed Prod1 1657703.06 1269089.75 388613.31 76.56
ShrinkFile commands Run:
DBCC Shrinkfile (N'Prod1', 0, Truncateonly)
Result: (file did not shrink)
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages 5 1 212185992 384 162439904 162439904
Additional ways I've tried running the shrink file:
DBCC SHRINKFILE (N'Prod1' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'Prod1' , 1396001)
DBCC SHRINKFILE (N'Prod1' , 0, NOTRUNCATE)
DBCC SHRINKFILE (N'Prod1' , 1600000) -- done to try a smaller shrink amount
Questions
I would expect if this was indeed shrinking the file by almost 400GB, SSMS would take more than 10-15 seconds to run, it would take hours before they query completes correct?
Is there some sort of data type issue that might prevent a shrink from working? I found this article: Deleting LOB Data and Shrinking the Database and it mentions LOB data (there is LOB data in that file) but this should just take a longer time to complete, nor prevent a shrink as I see it.
Anything else I am missing here? The syntax works correctly when I try it on my laptop, albeit with my small test DB, so its not the syntax of the commands from what I can tell.
