Sql-server – MS SQL Server: DBCC ShrinkDatabase – Does it really not shrink the database below the minimum size

dbccshrinksql server

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?

Best Answer

  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:

Related Question