table size not reducing after purged table

Question

I recently perform a purging on my application table. total record of 1.1 millions with the disk space used 11.12GB.

I had deleted 860k records and remain 290k records, but why my space used only drop to 11.09GB?

I monitor the detail on report - disk usage - disk space used by data files - space used.

Is it that i need to perfrom shrink data file? This has been puzzle me long time.

Answer 1

You need to explicitly call some operation (specific to your database management system) that will shrink the data file. The database engine doesn't shrink the file when you delete records, that's for optimization purposes - shrinking is time-consuming.

Answer 2

I think this is like with mail folders in Thunderbird: If you delete something, it's just marked as deleted, but to get higher performance, the space isn't freed. So most of your 11.09 GB will now contain either your old data or 0's. Shrink data file will "compress" (or "clean") this by creating a new file that'll only contain the actual data that is left.

Answer 3

Probably you need to shrink the table. I know that SQL server doesn't do it by default for you, I would guess this is for reasons of performance, maybe other DBs are the same.

Answer 4

For MS SQL Server, rebuild the clustered indexes.

You have only deleted rows: not reclaimed space.

DBCC DBREINDEX or ALTER INDEX ... WITH REBUILD depending on verison

(It's MS SQL because the disk space report is in SSMS)