2

This question already has an answer here:

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

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

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

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

|improve this question

marked as duplicate by hot2use, LowlyDBA, Erik Darling sql-server Jul 26 '18 at 1:57

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Did you observed any blocking when you were performing Shrink operation,here link in this article by Kendra little one reason that's listed is Deadlock which doesn't even gets reported – Shivam Kumar Jul 25 '18 at 16:39
  • The most likely thing to keep a file from shrinking is that there is actively referenced data at the end of the file. TRUNCATE only chops off unused space at the end of the file. IMO, the best bet is to use the NOTRUNCATE option, which simply reorganizes the data, then try the TRUNCATE option - both preferably when you are the only person on the system. If this doesn't do it, then its time to look at more obscure issues. – Laughing Vergil Jul 26 '18 at 0:05
  • One final note - you may want to play hardball with the table, and try migrating all of the data in it to another file in the filegroup, even if that means you need to add a new drive and file. AFAIK, this is the 'sledgehammer' solution to this issue, but it isn't always practical. – Laughing Vergil Jul 26 '18 at 0:07
  • I did not observe blocking or deadlocking when this was run. it was done on a staging server with no active transactions in order to test this. NoTruncate was also run prior to the truncate option for the reason laughing virgil mentioned, to no effect. Thank you for the post about the Ghost cleanup, I will go down that path. i had not seen that post in my searches. Also, thank you for the formatting help with the SQL code I posted, this is one of my first posts, and missed how to do it. – DBA Greg14 Jul 26 '18 at 14:20

Browse other questions tagged or ask your own question.