How It Works: FileStream (RsFx) Garbage Collection–Part (2)

In a previous post I outlined the basics of File Stream, garbage collection: This post continues the discussion, outlining specific details as to how the garbage collection progresses. A Single GC Thread The instance of SQL Server contains a single, background worker performing the garbage collection activities.   The FSGC worker, wakes up every ~5…


The Case of Anti-Virus filter drive interference with File Stream Restore

“Denzil and I were working on this issue for a customer and Denzil has been gracious enough to write-up a blog for all of us.” – Bob DorrFrom Denzil: I recently worked with a customer on a Database restore issue where the database being restored had 2TB of File stream data. The restore in this…


How It Works: FileStream (RsFx) Garbage Collection

In a previous blog I outlined how file stream transactions work and retain the before and after images of the file to allow various forms of recovery.  Reading that blog should be considered a prerequisite: I have had several questions lately about how the files get cleaned up as it is possible to build…


Secondary Access to File Stream(s) File, In Open Transaction, May Hang

This post is directly from an issue I have been working on.  The behavior is very difficult to simulate because of the flags and timings involved.   In this post I will attempt to describe the scenario and provide you with a simple workaround for your applications. Reproduction Steps 1. Begin a SQL Server transaction 2….


How to use FileStream feature in an ASP.NET Web Application that uses Forms or Anonymous authentication

We have a customer who uses Form based authentication in his ASP.NET application.   But he also wants to use FileStream feature.   Authentication becomes a challenge.   In order for FileStream to work, sql connection needs to use ingetrated security (not SQL Server based login). Here is the post.When you use Anonymous Access and Form based authentication,…


How It Works: File Stream Compression with Backup/Restore

The question of file stream compression during backup and restore arose last week and makes for a good clarification topic. Shown here is a conceptual layout of a database containing file stream containers.    On the left the folders represent the storage of the individual files used when varbinary(max) has the FILESTREAM attribute.   The FSAgent is…


How It Works: File Streams Requires Integrated Security (Windows Authentication)

SQL Server authentication versus Windows Authentication seems to cause File Stream users confusion.   I thought this e-mail chain would help. From: Robert Dorr Subject: RE: OpenSqlFilestream problem   It is really not the SQL Server Service account that matters here.   SQL Server has to have access to the files in order to handle the file…


How It Works: Debugging SQL Server Stalled or Stuck I/O Problems – Root Cause

Previously I have covered stuck and stalled I/O issues in other posts and articles.  However, these only tell you that there is a problem outside the SQL Server engine.   This post attempts to extend your root cause debugging capabilities beyond the SQL Server process. NOTE: Consult with your Windows Administrator before considering these…


How It Works: File Stream the Before and After Image of a File

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. The question that spawned this entry was: “I am performing updates of a specific row and column and after 5 updates I see 5 files for the…