SQL Server FileStream GC Made Easy

In this post I want to explain FileStream System Procedures added in SQL Server 2012. Before jumping into this topic we will spend couple of mins to understand what is FileStream and the issues we might encounter with SQL Server 2008 R2 version?

FileStream is the ability to store unstructured data like documents, images on the file system but still be managed by SQL server.
Here are some of the advantages:

• Store and retrieve BLOBs together with relational data in a single data store
• Unstructured Data is included in backup and restore operations 
• Crud operations on BLOBs and relation database occurs on the same DB Transactions
• NTFS File stytem is faster compared to SQL Server especially with the File Operations
• No limit on the file size but the applications can enforce this limit
• .Net APIs like SQLFileStream can be leveraged to interact with FileStream Objects

By default this feature is disabled and can be enabled either by using SQL Statements or by Management Studio. I guess I am going out of this topic, lets come back.

I am sure who ever leveraged FileStream feature in SQL Server 2008 or 2008 R2 might faced issues or might be looking for a better way for initiating Garbage Collection. There is an interesting blog on FileStream Garbage Collection and how it works. 

With SQL Server 2012 we have new set of procedures which helps us to initiate the FileStream Garbage Collection:

sp_filestream_force_garbage_collection:
        - This system procedure forces the garbage collector to run and delete unneeded files. The FileStream garbage collector runs automatically but if you want to call explicitly you can do it by using this procedure.

sp_kill_filestream_non_transacted_handles:
        - This system procedure helps you to close the non transaction FileStream handlers.

These two System Procedures are very useful for any Database team leveraging FileStream Feature.