A SQL Server DBA myth a day: (18/30) FILESTREAM storage, garbage collection, and more

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Phew – starting to get tiring having to make sure […]

What is FSAgent.exe and why does it have lots of waits?

Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is […]

Misconceptions around FILESTREAM storage

This short post is prompted by a question that came in through Twitter – I *knew* it was worth joining and spending time on it (http://twitter.com/PaulRandal). The (paraphrased) question is "can FILESTREAM data be stored remotely?". This has been confusing people, and neither FILESTREAM BOL nor my FILESTREAM whitepaper (see here) explicitly answer the question. […]

FILESTREAM garbage collection

In my previous posts on FILESTREAM I discussed the directory structure of the FILESTREAM data containerĀ and how to map the directories to database tables and columns. In this post I’m going to explain how and when the FILESTREAM garbage collection process works as that doesn’t seem to be documented anywhere (even in the FILESTREAM whitepaper […]

FILESTREAM directory structure – where do the GUIDs come from?

I’m teaching the Microsoft Certified Masters – Database qualification this week here in Redmond, and in part of day one I discuss the FILESTREAM directory structure. I was asked the question where do the directory name GUIDs come from? so I started digging around in the system tables while Kimberly was lecturing. Take a look […]

FILESTREAM directory structure

After writing the FILESTREAM whitepaper for Microsoft, I’ve had lots of questions about the structure of the FILESTREAM data container. The FILESTREAM data container is the technical term for the NTFS directory structure where all the FILESTREAM data is stored. When you want to use FILESTREAM data, you first add a filegroup (during or after […]

TechNet Magazine: April 2009 SQL Q&A column

The April edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]

Couple of nasty FILESTREAM bugs fixed in 2008 RTM CU1

Cumulative Update 1 for SQL 2008 RTM contains fixes for two nasty FILESTREAM bugs (among a lot of other bug fixes). The first one concerns restoring a 2008 database from a series of log backups when the database contains FILESTREAM info. It’s possible that a race condition can cause one of the log backups to […]

SQL Server 2008 FILESTREAM whitepaper published on MSDN

  Just found out that the 25-page FILESTREAM whitepaper I wrote recently for the SQL team has been published on MSDN. You can get it at https://msdn.microsoft.com/en-us/library/hh461480.aspx. Enjoy! Here’s the table of contents. Introduction Choices for BLOB Storage Overview of FILESTREAM Dual Programming Model Access to BLOB Data When to Use FILESTREAM Configuring Windows for […]

RunAs Radio interview on FILESTREAM

This is the second of the two sessions I recently recorded with Richard and Greg on RunAs Radio (the first one on being an “involuntary DBA” is here). I’ve just finished the final edited version of a whitepaper for Microsoft on the FILESTREAM feature of SQL Server 2008 and this session goes into details of why […]

High-performance FILESTREAM tips and tricks

Over the last few weeks I’ve had lots of questions about FILESTREAM performance and how to coax NTFS into scaling nicely. I just finished writing a 30-page whitepaper about FILESTREAM for the SQL Server team, which should be published before PASS in November (I’ll blog the link when I have it). Although my whitepaper isn’t […]

SQL Server 2008: How to get FILESTREAM and partitioning to work

Now we’re back from Iceland and I have a week to catch up with some content development before the MVP Summit next week and then SQL Connections the following week. One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get […]

SQL Server 2008: FILESTREAM performance

During the various courses I've been teaching, people are interested in how FILESTREAM performance compares with storing BLOBs in the database itself. I have some performance graphs based on measurements the dev team made – these have been presented publicly by myself and the dev team so I can share them with you here. There […]

SQL Server 2008: Changes to enabling FILESTREAM from CTP-6 onwards

Just over a month ago I posted on how to enable FILESTREAM in CTP-5 and the pre-CTP-6 build I was working with. Now that’s all changed! CTP-6 is a hybrid of the CTP-5 method and what will eventually be the methodology in RTM. The changes were made to separate the OS-level configuration from the SQL-level […]

SQL Server 2008 JumpStart

Phew – last week Kimberly and I spent 3 days teaching the ins-and-outs of SQL Server 2008 for DBAs/IT-Pros to about 130 Microsoft SQL Server experts and MVPs (like Kalen Delaney, Adam Machanic and Ron Talmage). This was the (95% complete) Beta delivery of a course we’ve been developing for the last six months for Microsoft […]

SQL Server 2008: Enabling FILESTREAM through T-SQL and SSMS

The second post in my series on FILESTREAM (see here) deals with how to enable FILESTREAM through T-SQL and using SSMS. You can’t just create FILESTREAM data – you have to specifically enable it at the instance level and then possibly restart the instance or host computer. The reason for this is that there’s a […]


Well it’s the end of our first week in China – tomorrow we fly to Beijing for another week of teaching. Hopefully we’ll get to do some sightseeing on Sunday – the last time I was in Beijing (for TechEd ’06) the conference hotel was about 25 miles outside the city centre – so I […]