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?
Best Answer
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.