July 17, 2018 at 7:15 am
I'm encountering an issue with shrinking a data file. There were large tables created in the wrong filegroup that needed to be moved to a different filegroup, which left the .MDF file with over 300GB available space, which will not be used in the future, so I would like to shrink the file to recoup that space. I am testing this on an exact copy of production on a new server we are migrating to soon. I have attempted to run every permutation I can think of to get this to work, but the shrinkfile command only runs for 10-15 seconds, returns a valid output (no errors), but the file size did not shrink. This is a DB that is not actively used yet, so I don't suspect blocking as an issue.
Some metrics:
File Size: 1647703 MB
Used Size: 1269090 MB
Available MB: 388613
Initially I ran DBCC SHRINKFILE (N'prod1', 0, truncateonly)
and that ran for 8 seconds and returned the normal result set, but the file didn't shrink. at all when looking at it in file explorer. I would have expected for this to run for a lot longer than 8 seconds if it was actually shrinking the file by almost 40GB. The file size did not shrink in file explorer.
I then thought it might be due to the fact that the free space was not at the end of file, so truncateonly could not shrink the file, so I ran the same command below with notrucate to move the data pages earlier in the file, and that again ran for 8 seconds.
DBCC SHRINKFILE (N'prod1', 0, notruncate)
I re-ran the truncateonly command, and that still didn't shrink the file. I have tried to run the command with an target size of 1396001 specified, and the same results happened, no errors were displayed, but the file did not shrink.
I've tested this on my personal laptop and my data file shrinks as expected with the syntax above, so i am not sure what is causing this. I am logged in using SA, which has sysadmin rights.
July 17, 2018 at 8:12 am
What is the Initial Size set to for the file?
Thanks
July 17, 2018 at 8:44 am
NorthernSoul - Tuesday, July 17, 2018 8:12 AMWhat is the Initial Size set to for the file?Thanks
The initial size is currently at 1672986 MB according to the properties window in SSMS. As i understand it, that is not the original size of the DB file, so it should be able to be shrunk below that size, correct?
July 17, 2018 at 8:54 am
gloeffler - Tuesday, July 17, 2018 8:44 AMNorthernSoul - Tuesday, July 17, 2018 8:12 AMWhat is the Initial Size set to for the file?Thanks
The initial size is currently at 1672986 MB according to the properties window in SSMS. As i understand it, that is not the original size of the DB file, so it should be able to be shrunk below that size, correct?
Try changing that and then run your shrink.
Thanks
July 17, 2018 at 9:08 am
What should it be changed to? If I reduce the initial size value, doesn't that immediately try to reduce the size of the file? Increasing that value grows the file, which would have the opposite effect of what I am trying to do unless you are saying that the file needs to be larger in order to start the shrinking, which i don't understand.
July 17, 2018 at 11:35 am
When you add the keyword, you're probably preventing the physical shrinking. If you really want to shrink, just shrink:
DBCC SHRINKFILE (N'prod1', 0)
I wouldn't use 0 personally except in extremely rare cases, such as no tables will be stored here and you really need to empty the file. Otherwise, use DBCC SHOWFILESTATS to see how much is being used, pad that amount a little (to allow some future growth), and shrink to that amount.
Edit: But 0 will work without causing any direct errors or problems, it will just longer than leaving a few empty mb in the file so SQL doesn't have to compact every since page/extent.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 17, 2018 at 12:07 pm
gloeffler - Tuesday, July 17, 2018 9:08 AMIf I reduce the initial size value, doesn't that immediately try to reduce the size of the file? Increasing that value grows the file, which would have the opposite effect of what I am trying to do unless you are saying that the file needs to be larger in order to start the shrinking, which i don't understand.
Changing what is displayed as the initial size would do a dbcc shrinkfile to the size you specified. So you may want to leave that alone for now.
I wouldn't try doing anything other than just shrinking down the size you specified as Scott already posted for you. You can also try to shrink it in smaller increments.
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply