SQL Express - Shrink Database vs Shrink File

Question

SQL Express - Shrink Database vs Shrink File

John 6

What's the difference between
Shrink Database:

And Shrink File:

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!

Manny Lopez 236 Reputation points

2022-09-07T00:01:27.82+00:00

Hi John,

Did you ever figure this out? I'm having the same issue. Can't shrink the DB file and the statistics between shinkdatabase and shinkfile don't match.

Manny
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-09-07T21:17:18.717+00:00

It's not a very good idea to piggyback on an old thread. Rather, I recommend that you start a new thread, describing your problem from start to end.

2 answers

Your answer

Manny Lopez 236 Reputation points

2022-09-07T00:01:27.82+00:00

Hi John,

Did you ever figure this out? I'm having the same issue. Can't shrink the DB file and the statistics between shinkdatabase and shinkfile don't match.

Manny
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-09-07T21:17:18.717+00:00

It's not a very good idea to piggyback on an old thread. Rather, I recommend that you start a new thread, describing your problem from start to end.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

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.

John 6 Reputation points

2022-02-17T17:59:27.38+00:00

Thanks for the reply.
The log files don't appear to be an issue, they're measured in KB.
The sp_spacedused command returns the same information as the Shrink Database GUI option.

We have deleted a lot of data in this database, and I've checked the indexes to repair the impact of the shrinking.
We were concerned about bumping into the 10GB limit for an Express database and just not sure which "Available free space" to believe.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-02-17T22:04:35.92+00:00

If you have deleted a lot of data that is a legit reason for shrinking.

Then again, the only reason you would bump into the 10 GB limit is that you fill up the space again. That is, if the data file is 9.5 GB in size it will remained 9.5 GB until you will up that space.

I have no idea how the deep backend of SQL Server works, but is it possible that it allocates space like an OS formats a disk? So when it says it has allocated space that only means it has reserved space in its "file allocation tables", but the Shrink File option shows how much space is really free among the allocated space.

SQL Server allocates a file in the file system just like any other application would do. Then it organises this file into extents of 64 K. Each extent has 8 pages of 8192 bytes. Most extents are for a single table, but the first page of a table may end up an extent with other tables.

When a table needs space, it allocates one of the free extents for that table.

As long as there are free extents, the file will not grow in the file system.

DBCC Shrinkfiles compacts the pages and extents, moving them to an early part of the file. Once this is done the contiguous set of empty extents at the end of the file are removed from the file and returned to the file system.
John 6 Reputation points

2022-02-18T20:29:08.113+00:00

Thanks for explaining the relationship between tables and extents.
When I run DBCC Shrinkfiles I get the following results:

If my math is correct, based on a 8192 byte page is roughly 9 GB, or what the GUI was reporting for the allocated space.

EXEC sp_spaceused reports a 8672.50 MB database size and 3.70 MB of unallocated space.
Still nothing matches or explains the meaning of the available free space from the Shrink File GUI
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-02-18T22:22:24.517+00:00

I used Profiler to see what queries SSMS submits to get the database size and the space used. I found that windows for Shrink Database and Shrink File submitted quite different queries. Well, in a way it is logical if you have umpteen files in your database and you only want to shrink one.

Why they did give so different results, I don't know. Possibly there are some tables/views to which the update is deferred, so if you click a UI too quickly, you may see outdated numbers.

As for the result set from DBCC SHRINKFILE, I will have to admit that I have never bothered to learn how to interpret. I just use sp_helpdb to see which file size I got.

Answer 2

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

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

John 6 Reputation points

2022-02-17T18:10:11.523+00:00

Thanks for the reply.
I can confirm that shrinking had some impact on indexes, but I've repaired those after several shrink attempts.
The log files aren't an issue as their only KB in size.

I have no idea how the deep backend of SQL Server works, but is it possible that it allocates space like an OS formats a disk? So when it says it has allocated space that only means it has reserved space in its "file allocation tables", but the Shrink File option shows how much space is really free among the allocated space.

Share via

SQL Express - Shrink Database vs Shrink File

2 answers

Your answer