SQL Server Performance Forum – Threads Archive

Dropped tables but free space is still 0

My understanding of SQL Server databases is that the data and log files have a fixed size that does not change unless the data files have been configured to Automaticaly grow and/or Autoshrink. Any allocated space in the data files that is not in use by the data objects would typicaly display as ‘Space Available’ in the Enterprise Manager properties box. I recently restored a 58GB database backup to a new test server for the purpose of cleaning up data by dropping redundant tables to reduce my database size.
I dropped more than 300 tables (which is approximately half of the tables in the DB) and many of these tables were quite large (million+ records).
I had expected to free up between 10GB and 20GB of free space… and yet the database properties still display my database size as an unchanged 58GB with 0.00MB Space available.
I ran DBCC CHECKDB WITH ALL_ERRORMSGS command overnight and it returned a report indicating no errors on the database. I have not attempted to shrink the database yet but I am not expecting much success in that regard due to the indication that there is no free space in the data file and therefore the database is unlikely to shrink at all. Any assistance or suggestions for shrinking this database and/or establish its true size would be appreciated. —————————————————
@@VERSION=Microsoft SQL Server 2000 – 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
—————————————————-

---

Run DBCC UPDATEUSAGE(0) in the database. Looks like SQL Server is reporting the space incorrectly.

---

Many thanks, DBCC UPDATEUSAGE(0) solved my problem perfectly.

---

]]>