Share via

SQL Express - Shrink Database vs Shrink File

John 6 Reputation points
2022-02-16T18:24:42.973+00:00

What's the difference between
Shrink Database:
175094-shrink-database-free-space.png

And Shrink File:
175082-shrink-file-free-space.png

I’ve shrunk all the files using Shrink File (Data, Log, and FileStream data), but doesn’t make any difference.

What has me nervous is Shrink Database saying 0% available free space.
Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 102.1K Reputation points MVP
    2022-02-16T22:45:48.71+00:00

    SHRINKDATABASE is pretty useless in my opinion. Normally, you only have reason to shrink either the data or the log files. And keep in mind, that even these cases are far and few in between.

    Shrinking a data file make sense if you have deleted a lot of data, and you know that this data will not be replaced by new data. For instance, you took a copy of production, and then cleared out 90% to get a small test database.

    Shrinking a log file makes sense if the log grew by some accident, and you have taken measures to prevent that accident from happening again.

    But shrinking something that will grow again, that's pointless.

    You also mentioned filestream. You cannot shrink filestream areas, but then again, they only take up the space they need to take up since data is stored as files.

    Why the two dialogues have so conflicting opinion on free space, I don't know. You can use the command sp_spaceused to investigate how much space you are using.

  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-02-17T02:57:44.16+00:00

    Hi @John ,

    >What's the difference between Shrink Database and Shrink File

    •DBCC ShrinkDatabase(): shrink all files in this database
    •DBCC ShrinkFile(): just one file

    For example, you may have a log backup issue and it's grown out of control so you run DBCC ShrinkFile(). A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database. Please read below blog to get more.

    What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

    >What has me nervous is Shrink Database saying 0% available free space.

    Shrink Database
    Available free space displays the sum of free space in the log and data files of the selected database.

    Shrink a File
    For data files, displays the current available free space computed from the output of DBCC SHOWFILESTATS(fileid). For log files, displays the current available free space computed from the output of DBCC SQLPERF(LOGSPACE).

    175202-screenshot-2022-02-17-104943.jpg

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".