Deleting column doesn't reduce database size

Question

I have a test database with 1 table having some 50 million records. The table initially had 50 columns. The table has no indexes. When I execute the "sp_spaceused" procedure I get "24733.88 MB" as result. Now to reduce the size of this database, I remove 15 columns (mostly int columns) and run the "sp_spaceused", I still get "24733.88 MB" as result.

Why is the database size not reducing after removing so many columns? Am I missing anything here?

Edit: I have tried database shrinking but it didn't help either

Yaroslav · Accepted Answer · 2013-01-24T08:28:14.467

25

Try running the following command:

DBCC CLEANTABLE ('dbname', 'yourTable', 0)

It will free space from dropped variable-length columns in tables or indexed views. More information here DBCC CLEANTABLE and here Space used does not get changed after dropping a column

Also, as correctly pointed out on the link posted on the first comment to this answer. After you've executed the DBCC CLEANTABLE command, you need to REBUILD your clustered index in case the table has one, in order to get back the space.

ALTER INDEX IndexName ON YourTable REBUILD

edited Jan 24 '13 at 08:28

answered Jan 24 '13 at 08:19

Yaroslav

6,476
10
48
89

1

I found a similar solution at this link: http://stackoverflow.com/questions/807579/how-to-reduce-size-of-sql-server-table-that-grew-from-a-datatype-change I am trying it now! Thanks! – Vinod Jan 24 '13 at 08:21
@Vinod, if it was useful don't forget to check the answered checkmark so others can be sure it was the correct answer. – Yaroslav Jan 24 '13 at 09:28
1

Well, I ran the query and the database grew to 45GB. I ran shrink database again then it has come back to the original 24733.88MB. I am not sure what's happening!!! :-( – Vinod Jan 24 '13 at 09:48
1

Take a look [here](http://blogs.msdn.com/b/sqlprogrammability/archive/2009/05/18/why-did-the-size-of-my-indexes-expand-when-i-rebuilt-my-indexes.aspx) and [here](http://stackoverflow.com/questions/7579/reorganise-index-vs-rebuild-index-in-sql-server-maintenance-plan) to find what can be happening, although maybe is not exactly your same scenary. – Yaroslav Jan 24 '13 at 10:16

score 3 · Answer 2 · answered Jan 24 '13 at 08:22

When any variable length column is dropped from table, it does not reduce the size of table. Table size stays the same till Indexes are reorganized or rebuild.

There is also DBCC command DBCC CLEANTABLE, which can be used to reclaim any space previously occupied with variable length columns. Here is the syntax:

DBCC CLEANTABLE ('MyDatabase','MySchema.MyTable', 0)WITH NO_INFOMSGS;
GO

Raj

score 1 · Answer 3 · edited May 23 '17 at 12:17

1

The database size will not shrink simply because you have deleted objects. The database server usually holds the reclaimed space to be used for subsequent data inserts or new objects.

To reclaim the space freed, you have to shrink the database file. See How do I shrink my SQL Server Database?

edited May 23 '17 at 12:17

Community

1
1

answered Jan 24 '13 at 08:17

Matt

1,648
12
22

Like I mentioned in my edit, I have already tried shrinking my database and log files. It didn't help! – Vinod Jan 24 '13 at 08:20

Deleting column doesn't reduce database size

3 Answers3