Hi experts,
This is 2012 Enterprise edition.
I have a database which has 1 data file - the initial size was set to 3.5 TB. But the actual data used size is 2 TB. Because other databases are on the same drive, I need to recover 1 TB of space on this drive. Shrinking is not a good option for obvious reasons. What are some other options, possibly copying all tables over to a new database that is sized appropriately? And what are the steps involved to do that?
(I don't have the option of asking for more storage)
Thanks.
redgate tools might something to look into. free 14 day trial or buy license it is worth it
I dont work for redgate 
1 Like
Thanks, yosiasz
Shrinking may be a good option, try doing smaller chunks, like 10 GB at a time.
You can migrate your database object from one database to another by Copy Database Wizard. If you are migrating your database regularly, then the process is quite time-consuming, So for faster migration of database, you can try SysTools SQL Database Migration Tool for easy copying of all database object from one database to a new database. Software gives you two option for exporting, either export your records to existing database or in new database.
You may be able to move tables to another filegroup by rebuilding the clustered indexes on them. Although it's worth bearing in mind that if you do that (or create another database) you'll probably need enough disk space to hold both copies during the transition.
If that's not an option, sometimes shrinking the database and then fixing up performance issues afterwards is just the only practical way to do it. When you're trying to reclaim a third of the disk space you've used, sometimes you have to take the least worst option.
Why is shrinking a bad option? I've done shrinks on 1 TB + transaction systems. The database remains online and usable. There's some extra disk usage for six hours or so and then I get a few hundred gigabytes back. If there are any issues I can cancel it.
Bill (graz), I've never liked the practice of shrinking data files. But I have done several incremental shrinks of this 2 TB data file. it takes 30 minutes to reduce the size by only 10 GB, so that's not an ideal option.
But I have completed this project. I created a temp db sized at 700 GB then copied the 1 table over. Deleted the other db and renamed the temp db to the production name. That worked well. But that would not be an easy solution if I had 100s of tables in the data file.
It's more about all the knock-on side effects of a poor algorithm used for shrinking than the basic concept itself:
Simple approach-Use a recovery tool.