0

In the documentation to DBCC SHRINKDATABASE, Microsoft is warning to run the command because it's i/o expensive.

However, in the documentation to DBCC SHRINKFILE, there is no such warning.

Now when we use simple recovery and basically have only one database file, is it better to run DBCC SHRINKFILE or DBCC SHRINKDATABASE?

askolotl
  • 255
  • 1
  • 2
  • 9

2 Answers2

3

A database can (and is) composed of multiple files. So shrinking the database would shrink all the files it's composed of, whereas shrinking a file is more granular (as Tibor pointed out). Therefore shrinking a database will always be at least as heavy or heavier of an operation than shrinking just a specific file of the database.

That being said, it's generally recommended against to shrink either because they're both heavy operations, generally nonsensical to do because if they grew to a certain size they'll likely grow back to that size soon anyway and a growth operation is also heavy.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • 1
    Thanks for the 1st paragraph. The 2nd paragraph is not true for us, because an operation ran out of control and wasted 200GB. Since there are backups/snapshots done every 4 hours, it would help us to reduce the file size. – askolotl Dec 14 '20 at 12:25
  • 1
    @askolotl No problem. Copying my comment for historical reference for others: I've been there (abnormal space wasting operation) and the experts still provided me the same warnings I'm passing on to you. I would be lying if I didn't say I still used the occasional shrink once in a blue moon anyway, but it's still important to be aware of the warnings, especially with potential data loss when shrinking the MDF. – J.D. Dec 14 '20 at 12:44
  • Also, 200 GB is really not a lot of space these days, especially with storage so cheap. Even if it was 2 TB, the warnings above would still apply and the experts would generally advise against doing it. I wouldn't doubt you'll see that 200 GB be used up again, I just couldn't tell you when since I don't know your data. Personally I like to go by the mantra unused server resources are wasteful, if you're paying for them anyway, might as well use them. But it's for you to decide if you want to reclaim the space for now, and it's not the end of the world to do a shrink operation. – J.D. Dec 14 '20 at 12:49
  • J.D., I'm curious about the corruption risk. Do you have a pointer or reference to this? AFAIK, shrink should be safe and it is transaction protected by small transactions. (Of course it doesn't mean we should do it unless there's a special situation.) Any such case should be considered a bug and, perhaps there's some old myth leftover from a bug that was fixed ages ago? – Tibor Karaszi Dec 14 '20 at 16:46
  • @TiborKaraszi I have to look back, and I'll link it when I find it, but I was definitely advised this by one of the DBA.StackExchange power users who had a lot of street cred. Maybe it is just a thing of the past when disks were more likely to be spinning platters. :) I'll update my answer if I find otherwise. – J.D. Dec 14 '20 at 19:02
  • 1
    @J.D. Cool. If you find something, let us know. I know how it is, you see something, it gets engraved as a "truth", and after a while it might not be relevant. Or it might still be... :-) – Tibor Karaszi Dec 15 '20 at 14:08
  • @J.D. 200 GB is much when you have the db running on a virtual machine which makes a snapshot every 4 hours. – askolotl Dec 15 '20 at 16:28
  • @askolotl That's fair, and to each their own circumstances. But when you shrink the file back down, the space just gets released back to the drive inside the VM and I'm assuming it was already provisioned on the VM before the file grew (otherwise I'm not sure where it would've gotten the space from, though I'm not an infrastructure expert). So I'm assuming that's all still part of the snapshot. The difference I'm assuming is how the empty space compresses in the snapshot both ways (which may or may not be the same...though I would guess not) in which case I understand where you're coming from. – J.D. Dec 15 '20 at 17:08
  • @TiborKaraszi After some extensive digging it seems my memory has been serving me poorly (I better download some more ;). I found some older answers to questions of my past that indicated data corruption is possible but unlikely. Better yet though, I found this conclusive answer from Josh Darnell that basically says while anything is possible it's impractical to consider that as a concern because it's only possible if there was a bug in the code. I've updated my answer and will keep this in mind going forward, thanks for causing me to refresh. – J.D. Dec 16 '20 at 00:23
  • 1
    @ask taking a snapshot won't be affected by unused space in the database. When you snapshot a vm, new writes to the vm file are written to the new snapshot file, so only changes are affected by snapshotting. BTW, what is your purpose for snapshotting? – Hannah Vernon Dec 20 '20 at 19:55
2

They both do the same thing in the end. But SHRINKFILE is better since it gives you better control. You decide which file to shrink and to what size you want to shrink that file.

Tibor Karaszi
  • 17,101
  • 2
  • 14
  • 26
  • I just wondered, because in the documentaton, Microsoft is warning to run SHRINKDATABASE, while there is no such warning for SHRINKFILE. – askolotl Dec 14 '20 at 12:01