How To Shrink a File to Below the Size value in SQL Server 2005

Hello
The database I am working on was created as a restore from a production backup.
The 'Initial Size' of the data file is automatically set to the actual size of the file (or close to it)
In my case, it is 100gb.
I have since added another File Group (and hence file) and moved over half of the tables to it.
I need to reclaim the free space in the original file.

The DBCC SHRINKFILE (N'myDatabase.Primary' , 70000) does not have any effect (presumably because it will not shrink to below the Initial Size)

When I try to reduce the original size using
ALTER DATABASE myDatabase MODIFY FILE (NAME = N'myDatabase.Primary', SIZE = 50gb)
 
I get the error 'MODIFY FILE failed. Specified size is less than current size.'

Has anyone got any ideas on how I can reclaim this free space when the Initial Size of the file is way too big

thanks in advance


 

vikings24Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

maradamCommented:
I'm afraid the only way is to migrate data and objects to another database.
0
vikings24Author Commented:
thanks maradam

Was hoping it wouldn't come to that, but I thought it may.

On a small database, I created a 2nd file in the Primary Group, then did a shrink file1 EMPTYFILE.
It wouldn't 'move all contents of file1 to other places', but I think it did move most of it.
I then did a shrink of file1 to a small size, and it did work.

I then did a shrink file2 EMPTYFILE, which moved it all back again to file1.

Next step is to try it on the original database (after I can get hold of an extra 100gb temporarily)

What do you think? - would this be preferable (assuming it works)  to migrating to a completely new database?

 
0
maradamCommented:
I did the same experiment on totally empty database. And I can't shink it below it's initial size. Emptyfile will never work in such a way to make you able to drop the file. It is primary data file and system objects on it will never be moved. So if you can't drop the file you will never be able to shink it below it's initial size. I still think migrating is your only choice.
0
vikings24Author Commented:
thanks maradam
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.