CODEWITHSUNDEEP
sql-serverdatabase
S. Benalla
S. Benalla

Reputation: 81

Database size after deleting rows does not change

I have 2 databases from which I have deleted rows in a specific table in order to decrease the size of the database.

After deleting, the size of DB.mdf does not change.

I also tried to rebuild the index and used cleantable, but to no effect!

ALTER INDEX ALL ON dbo.'Tablename' REBUILD

DBCC CLEANTABLE ('DBname', 'Tablename', 0)

Upvotes: 8

Views: 27012

Answers (4)

SpaceGhost440
SpaceGhost440

Reputation: 579

I wrote this after being in the exact same scenario and needing to shrink the database. However, not wanting to use DBCC SHRINFKILE I used Paul Randals method of shrinking the database.

https://gist.github.com/tcartwright/ea60e0a38fac25c847e39bced10ecd04

Upvotes: 0

Markus
Markus

Reputation: 1211

You need to shrink the db. Right click db, Tasks->Shrink database

Upvotes: 9

Jasmine John
Jasmine John

Reputation: 921

Even I faced the same issue, my db was 40MB after deleting some columns still its size was not getting changed.. I installed SQLManager then opened my db and used command 'vaccum' that cleaned my db and its size got reduced to 10MB. enter image description here

Upvotes: 0

StaticVariable
StaticVariable

Reputation: 456

Deleting rows in a database will not decrease the actual database file size.

You need to compact the database after row deletion.

Look for this After running this, you'll want to rebuild indexes. Shrinking typically causes index fragmentation, and that could be a significant performance cost.

I would also recommend that after you shrink, you re-grow the files so that you have some free space. That way, when new rows come in, they don't trigger autogrowth. Autogrowth has a performance cost and is something you would like to avoid whenever possible.

Upvotes: 7

Related Questions