Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have been tasked with migrating file data from one SQL server DB to another. There is currently a SQL server 2005 DB that is stored file in an image type field. There is a migration happening that is moving these files to a SQL 2008 Filestream setup, which works great. The files are being migrated in batches, but I have run into a problem as the file size of the DB is not decreasing. The migration script moves the batch then deletes the batch. The records are no longer available by query in the old database and they have moved successfully, but like I said the file size does not budge.

I need the size to decrease to allow for the next batch as the initial DB is nearly 1TB, there is just not enough room to have two copies.

I have tried SHRINKFILE, but that is not working. Any help is appreciated.

share|improve this question
    
What is the SHRINKFILE command you have used? You might need to do a TRUNCATE... –  Mark Henderson Jun 10 '09 at 22:57

3 Answers 3

up vote 1 down vote accepted

Try dbcc shrinkdatabase('mydbname')

This is a safe command though I wouldn't use it when the database is in heavy use.

JR

share|improve this answer

Shrink Database should do the trick. It should at least tell you how much you can reclaim.

share|improve this answer
    
It ran for 10 minutes and I received: "Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded." –  Dustin Laine Jun 11 '09 at 19:04

Is the transaction committed? If not, you can't reclaim that space.

Are you sure you're doing a shrinkfile on the right data file as well? Or are you doing a shrinkdatabase?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.