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?

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

10
  • 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? Dec 14 '20 at 16:46
  • 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... :-) Dec 15 '20 at 14:08
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.

1
  • 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

Not the answer you're looking for? Browse other questions tagged or ask your own question.