CODEWITHSUNDEEP
sqlsql-serverdatabasediskspace
beginner
beginner

Reputation:

table size not reducing after purged table

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.

Upvotes: 0

Views: 3595

Answers (4)

gbn
gbn

Reputation: 432742

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)

Upvotes: 3

1800 INFORMATION
1800 INFORMATION

Reputation: 135463

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.

Upvotes: 0

schnaader
schnaader

Reputation: 49731

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.

Upvotes: 0

sharptooth
sharptooth

Reputation: 170569

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.

Upvotes: 1

Related Questions