The sql server size file doesn't decrease when i delete my records

Question

I have a table called test i insert 40000 records in it ,I split my database file into two file groups like this :

The size of both files based on round robin algorithm increased 160 mb as you can see . after this i delete the data in my table .but the size of both file (FileGroup) remains on 160 mb .Why ?

You would need to shrink the data file to do that. Sql Server's data file almost always has space free in it so space is allocated on disk although that space is not being used by data. Once the file is too small for the data it will grow the file. If you want to release the unused but claimed space of the file you need to execute a shrink on that data file. — Igor, May 08 '17 at 10:18
That how the files work, shrinking them after a delete is very expensive as all existing data would need to be reorganised/rewritten. The files grow within their configured maximums/growth. — Alex K., May 08 '17 at 10:19
You mean when the data is deleted in my database i have to shrink my database ? — Ehsan Akbar, May 08 '17 at 10:19
No, you should leave the files alone and let the server manage them, its good at it. — Alex K., May 08 '17 at 10:19
The file sizes you are seeing is the container sizes allowed. If you need more space, these containers grow according to the autogrow settings, but if the data shrinks, the containers do not shrink. You have to shrink the files to make them smaller. — cloudsafe, May 08 '17 at 10:20

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

This is because SQL Server is assuming that if your database got that large once, it is likely it will need to do so again. To save having to go through the overhead of requesting space from the operating system each time SQL Server wants to use some more disk space, it will simply hold on to what it has and fill it back up as required unless you manually issue a SHRINK DATABASE command.

Due to this, using `shrink` is generally considered a bad idea, unless you are very confident your database will not need that space at some point in the future.

The sql server size file doesn't decrease when i delete my records

1 Answers1

Due to this, using shrink is generally considered a bad idea, unless you are very confident your database will not need that space at some point in the future.

Due to this, using `shrink` is generally considered a bad idea, unless you are very confident your database will not need that space at some point in the future.