SQL Server: How to shrink FileStream files?
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 videos on Youtube
Kafese Wub
Updated on September 18, 2022Comments
-
Kafese Wub almost 2 years
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.
-
Kafese Wub over 11 yearsI'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?
-
bkr over 11 yearswhat 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.
-
Kris over 5 yearssp_filestream_force_garbage_collection worked perfectly, and quick too! Cheers. Instantly freed up 130GB+ of garbage files. (You might possibly need to switch to Simple recovery model for this to work.)
-
Henry Ing-Simmons over 2 yearsI still get
The file cannot be removed because it is not empty.
if I then try to remove the file :( -
Henry Ing-Simmons over 2 yearsRemoving the files at the deepest levels seemed to unblock this
-
user2993805 over 2 yearsMaybe your DB is still active and repopulating the files?
-
user2993805 over 2 yearsOr, maybe your files are in a different location.