I encounter one weird problem, I have a database with around 7 GB ...
when I delete a bunch of data from it, it suppose to reduce the
database file size, but weirdly, the file size increase to 8 GB.

Wondering why. Is it suppose to be like that?
Is it the architecture is designed to work like that?

Is there any way for me to reduce the database file size?


Thanks.




Peter CCH

If you take back daily with append data option, the size gets increased

Madhivanan

What utility are you using to measure the database size?

Are you using sp_spaceused and just noting the "Database Size" column?
This also includes the transaction log, and this will increase when you
delete data from the database, until you either truncate or backup your
transaction log.

You can also get wrong space values from sp_spaceused if you are making
frequent and large changes to data, such as updates and deletes, as the
counters that record the new extent allocations and deallocations don't
get updated dynamically every time.

Try running sp_spaceused in the database, and note the values in all
columns.
Now, repeat this, but execute sp_spaceused @updateusage='true'

(N.B. This can take a few minutes to run. I have never had a problem
running this on a live database during the day, but be aware that it
runs DBCC UPDATEUSAGE and forces updates to the sysindexes catalog; it
is less-risky to run it out of hours)

Here's a good example from one of my databases:

sp_spaceused
go

database_name, database_size, unallocated space
Roms, 22541.00 MB, -4407.84 MB

reserved, data, index_size, unused
27589472 KB, 16362352 KB, 11146992 KB, 80128 KB

Note the negative Unallocated Space value.


Now I run:

sp_spaceused @updateusage='true'

database_name, database_size, unallocated space
Roms, 22541.00 MB, 1.43 MB

reserved, data, index_size, unused
23074376 KB, 15558360 KB, 7447464 KB, 68552 KB

You can see that, prior to this, the Reserved, Data and Index_Size
columns were all showing more space being used than the true value. I
suggest you try this after you delete your data in future, and see if
you get the values you expect.