4

When I delete rows from a table in SQL Server, the size of the database file does not get smaller. Why is that? Even if I delete every row from every table, the file size remains just as large as before the delete.

I'm interested in understanding why, not how to shrink the database file(s).

Improve this question
3
  • does this help or this ? Yes, just deleting, sql server will not release the space to OS. You have to shrink the database file (make sure you know the consequences of shrinking and when it is OK to shrink)
    – Kin Shah
    Apr 28, 2017 at 13:35
  • This is a gross over simplification but here it goes. Your data files are buckets and SQL Server is your sandbox. You fill the buckets with more and more sand, then you notice, you can't fill the bucket anymore. So you grab a bigger bucket, dump the old bucket into it. Perhaps afterwards you decide you don't want to have as much sand in that bucket as you put in it, so you empty it some. You still have that bigger bucket until you "shrink" it by replacing it with your smaller bucket.
    – Shaulinator
    Apr 28, 2017 at 13:36
  • I asked the same question 4-5 years ago here, dba.stackexchange.com/questions/21615/…
    – bugwheels94
    Apr 28, 2017 at 16:45

3 Answers 3

Reset to default
8

Space in database files is allocated as needed. When rows are removed from a table, that space is simply marked as unused inside the datafile. Datafiles do not shrink automatically (unless auto-shrink is turned on) because that requires extra disk I/O that is typically counter productive for performance. The assumption is that any unused space will at some point be re-used by new rows.

To summarize, space allocated to the file on disk, and the logical size of tables within the database are not tightly coupled.

In more detail (although still very simplified):

They are definitely related. Rows in a rowstore live on pages, which are 8k in size and arranged into extents. Extents are groupings of 8 contiguous pages on disk.

Rowstore rows lives on pages and when "deleted" are not physically removed from the file - just marked as "free" inside of SQL Server through the use of bitmap and bytemap allocation structures and SQL Server will re-use the space accordingly.

Improve this answer
3
  • that seems right. I mentioned "I know the only way to reclaim the disk space it is by shrinking the datafile", because i didn't want anyone to recommend me to shrink the file and save them time. I was looking for the science behind it and you have provided it.
    – PolDBQ
    Apr 28, 2017 at 13:57
  • @PolDBQ Ah, gotcha. It was a little confusing to me. Yes, SQL Server doesn't "give back" any space unless you shrink ;)
    – Sean Gallardy
    Apr 28, 2017 at 14:13
  • 1
    This is correct, but just to emphasize (since you mentioned it in passing), you should never ever ever EVER turn on AutoShrink!. Shrinking data files defragments tables, which you can manage somewhat when you have to shrink manually, but you have no control over with AutoShrink. Also, it can kick off any time of day, even in the middle of other busy transactions.
    – BradC
    Apr 28, 2017 at 16:00
3

Think of SQL data files as a commercial self-storage unit:

row of self-storage doors

Data from tables and indexes is organized into these units (called "pages"), and SQL server is very meticulous about keeping track of what belongs where.

If you fill up all the storage units, you can buy land and build more (expanding the data file), but what happens when some units get emptied?

  • If the empty unit is at the end of the row, sure, you could demolish the unit you built and sell off the land, but that's a lot of work, especially if you might need to buy it right back again.
  • But if the empty unit is in the middle of the row of storage units, you can't even do that without a lot of extra work. You'd have to move all the stuff from full units at the end of the row into the empty units in the middle before demolishing units and selling off the property.

Not only is moving all those units around a lot of (unnecessary) work, it also can cause table and index fragmentation, which can impact performance.

Imagine if you had a family that bought 10 units next to each other, and you later forced them to move 4 of those units to random empty units elsewhere in the facility. When they came to add or remove stuff from their units, they will have to do a lot of extra work wandering around between the scattered units.

Obviously this analogy isn't perfect, and clearly there are situations that you really do want to shrink a data file, but you don't want SQL server doing it on its own, you want to be in control of when and how exactly that happens. For example, you'd probably want to do a DBCC SHRINKFILE to a specific size, leaving some portion of internal free space, then follow immediately by a reindex/defrag of your tables/indexes, to fix any fragmentation you've just created.

Moral of this story: Never turn on AutoShrink!

Improve this answer
2
  • 1
    that's a proper metaphor to describe it.
    – PolDBQ
    May 2, 2017 at 18:30
  • @BradC : please copy your answer to the 'original' question. Your answer is very very nice
    – Jayan
    Nov 23, 2021 at 11:35
0

In simplistic terms, it's not like a text file where deleting a few rows of data and saving shrinks the file. SQL keeps the space allocated in the data file to give it free space to accommodate more data over time without having to grow the file again.

Think of it like a bucket - you don't go chopping the top off after you've poured out half of the water just because you don't need that extra six inches of plastic to hold the remaining water in.

Improve this answer

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