Sql-server – SQL Server: How to shrink FileStream files

filestreamshrinksqlsql serversql-server-2008

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.

Best 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.

Related Topic