I have a 31 GB database I got from a customer for a project. I'm coding and keeping the database in a 80GB Win7 VM. I got the db file and imported the database from that, and found that the space was being eaten up mostly by one table, with 240,000 images. I dropped most of those rows and now have 29,000MB available. I can't perform lots of functions because the drive is down to it's last few GB, and there are still a lot of records to modify (I'm changing a data type in several tables, but keep getting a active_transaction error). It's not shrinking via DBCC_SHRINKDATABASE(0) or Tasks > Shrink > Database or Files. It only shrinks to the size of the database when I first got it, it seems. How do I get it to release that space? If I can', can I export what data is still there to a smaller database, and then restore from that?
Running SQL Server 2012 and Management Studio.
Thanks,
Tony
You cannot dbcc shrinkdatabase below the initial size setting. If the initial size of your database is set to 50GB -- the MDF will always be at least 50GB, even if 29GB is free/available.
If you do in fact have free space, you can lower the initial size setting to a more reasonable value - but if the MDF will simply auto-grow again, this is not advised, as it incurs a high overhead.
To do this you would use dbcc shrinkfile.
Please see here for more information.