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

In a previous post I outlined the basics of File Stream, garbage collection: http://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx 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…

2

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…

2

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: http://blogs.msdn.com/b/psssql/archive/2008/01/15/how-it-works-file-stream-the-before-and-after-image-of-a-file.aspx I have had several questions lately about how the files get cleaned up as it is possible to build…

3

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

3

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,…

3

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…

3

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…

5

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. http://blogs.msdn.com/psssql/archive/2006/11/27/what-do-i-need-to-know-about-sql-server-database-engine-i-o.aspx http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx NOTE: Consult with your Windows Administrator before considering these…

2

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…

3