Database size bigger after delete from table

Question

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?

I don't know tsql but it may simply be a temporary transaction log. — Denys Séguret, Jun 02 '12 at 19:21
This is exactly the reason, after I delete temporary files it is OK right now. Thanks! http://stackoverflow.com/questions/56628/how-do-you-clear-the-transaction-log-in-a-sql-server-2005-database — Teoman shipahi, Jun 02 '12 at 19:24

score 2 · Accepted Answer · answered Jun 02 '12 at 19:28

2

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.

answered Jun 02 '12 at 19:28

Denys Séguret

score 2 · Answer 2 · edited May 23 '17 at 11:58

2

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.

edited May 23 '17 at 11:58

Community

answered Jun 02 '12 at 19:32

StuartLC

TRUNCATE is also logged. Where do people learn these myths that TRUNCATE is not logged? – Aaron Bertrand Jun 02 '12 at 21:00
@Aaron Thanks for pointing this out. I believe I know where I picked up this misconception - the SQL 2000 and previous command 'BACKUP LOG DB WITH TRUNCATE_ONLY' probably fused my (and other's) synapses into associating 'truncate' with bypassing logging. – StuartLC Jun 03 '12 at 06:48
[Truncate has significantly less logging though](http://dba.stackexchange.com/a/7677/3690) – Martin Smith Jun 04 '12 at 09:56

2 Answers2