SQL Server: How to shrink FileStream files?

Question

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:

I get this exception:

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.

Answer 1

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.

Answer 2

The problem is that the space won't free up until garbage collection is completed. Unfortunately there's no way force this.

I used a powershell script to iterate through the filestream files and empty their content. (note that this will remove the filestream data from all records, as long as you don't need it). This will leave the file that associated to the record in place, but will look like the field was empty.

You will need to change the path to your filesteam folder, but this is essentially what I used.

$files = Get-ChildItem -Path "C:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDbRestore_9.MyDbRestore_Filestream" -Recurse -File -Exclude "*.hdr"

ForEach ($file in $files) { Clear-Content $file.FullName }