CODEWITHSUNDEEP
sql-servert-sqlsize
Teoman shipahi
Teoman shipahi

Reputation: 23052

Database size bigger after delete from table

After I delete huge amount of data from a SQL Server database table, the database size increased.

When I run sp_spaceused it shows me 2625.25 MB. It used to be ~ 1800.00 MB before I deleted from table.

Is there any specific reason it keeps growing even if I delete data?

Upvotes: 1

Views: 7537

Answers (2)

StuartLC
StuartLC

Reputation: 107247

I'm assuming that you are using SQL Server (sp_spaceused). Deleting is logged, so your log file has grown.

See SQL Server 2008 log will not truncate on how to truncate your log (depending on your DB and recovery model), and then you can run

DBCC SHRINKFILE(N)

to reclaim lost space

Edit As per @Aaron, Truncating is also a logged operation. Answer corrected.

Upvotes: 2

Denys Séguret
Denys Séguret

Reputation: 382150

A temporary transaction log is often the reason for a notable increase of size after a huge delete.

It will eventually disappear on its own but you may remove the files if you need to reclaim the space.

Upvotes: 2

Related Questions