I am talking here about a DB which has a data file of 550GB in which around 460GB is unused. I want to shrink that data file to a level where there is only around 10% of unused space available. Currently the amount of unused space is over 85%. Below given are the options I have tried:
1) Using DBCC shrinkfile with a target size. Plan was to shrink the data file in chunks of 50GB for 7-8 times. -- Didnt work. ( no error but it was just not executing )
2) Using Import/Export data after creating a blank DB where only the data will be transferred and not the unused spaces -- Didnt work ( Because only the tables were getting imported, other objects like functions,procedures were not getting imported. )
3) Using Copy Database wizard by creating a destination database on the fly -- Didnt work ( As the unused space that was there in the Source database also got copied into the new destination DB )
4) Using DBCC Shrinkfile with emptyfile options -- Didnt work ( Because even though I was able to transfer data, I was unable to remove the source file as it a part of the primary group.)
5) Using DBCC Shrinkfile with truncateonly -- This options seems to be working, is there any precaution that you want me to take (other than a full backup) to make sure that nothing goes wrong in a prod server.
6) Last options left for me is I guess scripting all the objects of the whole database, which to me is a very expensive options.
I request you all to review my options and let me know as to what is the best option available. Incase anybody knows anymore options then please let me know about it.
Thanks in ADVANCE!!
SQL Server Developer DBA