0

Per Microsoft's documentation on DBCC ShrinkDatabase command, it shouldn't shrink the database below it's "minimum size".

But when I ran the command, my database data files' initial file sizes shrunk to a smaller size. Though I thought the initial file sizes were the "minimum size". Or am I misunderstanding Microsoft's wording / intentions, and DBCC ShrinkDatabase is considered a "size changing operation"?

Shrink a Database:

The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

I.e. When Microsoft says "The database cannot be made smaller than the minimum size of the database" is that a tautology and I'm just overthinking things?

4
  1. You shouldn't use DBCC SHRINKDATABASE. Shrinking files is a very extraordinary event and should be used with extreme caution and, even then, only with carefully planned commands that target individual files (DBCC SHRINKFILE is SHRINKDATABASE's friendlier, less crazy cousin.)

    See this post for lots of advice around this. Usually shrinking a file is only a very temporary measure and a totally wasted effort because the file(s) will just grow again.

  2. Minimum size and initial size are different things, and are treated differently by SHRINKFILE and SHRINKDATABASE. SHRINKDATABASE definitely will not let you go below the initial file sizes, whether you explicitly set them or inherited from model - if you are seeing this happen, please show a full repro, because I couldn't make that happen. I created the same 4GB file as below, tried a SHRINKDATABASE with a target of 99%, and it didn't budge. My guess is you got it below the current size but that was not the initial size.

SHRINKFILE will let you go below initial size. Here we create a 4GB data file, then try to shrink it down to 1MB.

    CREATE DATABASE floo ON 
    (
      name = floo_data, SIZE = 4096MB, filename = 'C:\...\floo.mdf'
    )
    LOG ON 
    (
      name = floo_log, SIZE = 1024MB, filename = 'C:\...\floo.ldf'
    );
    GO

    USE floo;
    GO
    SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
    GO

    DBCC SHRINKFILE(floo_data, 1);
    GO
    SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
    GO

Results show we don't quite get to 1MB, but we get far below both our initial size and the size set up in model (8MB in most modern systems):

enter image description here

One single operation made the file smaller than model (which is 8MB on my system), which is what one might expect either the minimum or initial size to be, depending on how the database is created (with or without explicitly specifying a file size).

Inheriting file size from model also allowed me to shrink the file below the initial size (which should double as the minimum in this case):

CREATE DATABASE blar; -- inherit file specs from model
GO
USE blar;
GO
SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
GO

DBCC SHRINKFILE(blar, 1);
GO
SELECT name,[size_on_disk] = size*8. FROM sys.database_files;
GO

Results again show we can get below the "minimum" size and the initial size:

enter image description here

Again I strongly recommend you really look into whether you should ever really be shrinking these files anyway. This really should be a quite exceptional event.

Further info on analyzing reasons for tempdb data/log growth:

  • Aaron, it's an honor to have you answer my question (I've heard a lot of good things about you in the SQL community :). While I agree SHRINKDATABASE shouldn't normally be used, and I believe I understand the ramifications of using it, I currently believe there are one-off cases where it might be useful (though I'm interested in learning otherwise, if I'm wrong.) My case that led me to SHRINKDATABASE is the classic TempDB that filled up it's drive from a one time (or rare occurring) operation that completed and then the left over space consumed by the SQL server was technically being wasted. – J.D. Jun 25 '18 at 18:31
  • Sorry this was meant to continue my response above: My T drive (dedicated to just TempDB) went from about 10 GB (about 5% of the drive size) free to 10 MB free after the one time operation in TempDB. Is it still not worth shrinking it to reclaim the space at that point? If not, what is best practice around this then. Is it ok to leave the drive with only 10 MB (0.005% of the drive size) free? If not, is it worth expanding the drive if it's not expected that TempDB will ever grow that big again? – J.D. Jun 25 '18 at 18:38
  • 1
    @J.D. Typically what people will do is they'll almost fill the drive, leaving room for one or two autogrowth events (which is easy to determine with sensible, e.g. not %, autogrow rates). Wrap an alert around the autogrow event and hopefully you catch it and determine the cause in real time, dealing with it however (kill the process, etc). If you have an event or set of activity that's going to fill your entire T drive, it doesn't matter if the file started at 99% of the drive or 50% of the drive, it's still going to fill the drive. – Aaron Bertrand Jun 25 '18 at 18:41
  • 1
    ... It's just going to take slightly longer to get there if it has to grow a bunch of times along the way. What people usually do (especially if they have workloads that are prone to these occasional blow-ups) is double or quadruple that storage and spread the tempdb files across multiple devices. This gives you a lot more breathing room, is not expensive (unless you're tied into a really crappy SAN), and makes it far less likely you'll ever fill the drive(s) again. – Aaron Bertrand Jun 25 '18 at 18:43
  • 2
    Some info here: (~1~) (~2~) (~3~) (~4~) (~5~) (~6~) (~7~) – Aaron Bertrand Jun 25 '18 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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