SQL Server Database size unchanged after dropping File column

Question

I have a database with a table that has about 10 000 records. One of the columns in this table is a VARBINERY(MAX) that is used to store images. A backup of this database is about 1.2GB. It's size as shown in SQL Server Management Studio is about 3.5GB.

Working on the assumption that the images make up the bulk of this size, I dropped the column. This did not alter the database's size at all. The backup and database size is still the same. How is this possible?

• John K. N.: Did you reorganize the indexes/table after dropping the column? What happened after a second backup? When you write the database size is 3.5 GB what are the individual sizes for the DATA and LOG portions of the database (Right-click database | Reports | Standard Reports | Disk Usage)? You might find a nice report their that shows the top tables.

Answer 1

When you drop a table, it will free the space in the database, but not back to the O.S. because your database has this space allocated. You need to shrink your database. but it's not a good Idea, because it's good to have free space in the database , so it can grow without the need to ask the o.s. to grow your file and then use it. this can cause fragmentation.

Answer 2

If you decide to shrink the database i'd recommend rebuilding the indexes or re-organise to avoid any fragmentation.