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.

For a project, I'm using a SQL Server 2008 R2. One table has a filestream column.

I've made some load tests, and now the database has ~20GB used.

I've empty tables, except several(configuration tables). But my database was still using a lot of space. So I used the Task -> Shrink -> Database / Files But my database is still using something like 16GB.

I found that it's the filestream file is still using a lot of space.

The problem is that I need to backup this database to export it on the final production server, and event if I indicate to compress the backup I got a file more than 3.5Go. Not convenient to store and upload.

And I'm planning much bigger test, so I want to know how to shrink that empty space.

When I'm trying: enter image description here

I get this exception: enter image description here

The properties SIZE, MAXSIZE, or FILEGROWTH cannot be specified for the FILESTREAM data
file 'FileStreamFile'. (Microsoft SQL Server, Error: 5509)

So what should I do?

I found several topics with this error but they was about removing the filestream column.

share|improve this question
add comment

1 Answer

Old versions of files are removed from filestream via a garabage collection process that runs during the checkpoint process.

see http://sqlskills.com/BLOGS/PAUL/post/FILESTREAM-garbage-collection.aspx for a full explanation.

so - you jump through all the hoops, run a log backup, checkpoint and then you... wait, because the stupid garbage collector only seems to delete files out at the rate of about 4 or 5 per second.

new in 2012, I think? is sp_filestream_force_garbage_collection ( http://msdn.microsoft.com/en-us/library/gg492195.aspx ) - but I haven't used it so I can't say how effective it is.

share|improve this answer
    
I'm sorry, this project has been paused for months. So I did a backup, a checkpoint, something like 2 hours ago, but the space is still not released, and the shrink file still doesn't work. What should I do? –  J4N Dec 10 '12 at 13:51
    
what kind of backup did you do, you need to remove all references to the file before it will garbage collect it. depending on your recovery mode, a full backup is not enough, you also need to do a transaction backup as well. –  bkr Mar 22 '13 at 18:39
add comment

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.