{"id":713,"date":"2010-04-18T10:22:00","date_gmt":"2010-04-18T10:22:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(1830)-FILESTREAM-storage-garbage-collection-and-more.aspx"},"modified":"2017-04-13T12:47:39","modified_gmt":"2017-04-13T19:47:39","slug":"a-sql-server-dba-myth-a-day-1830-filestream-storage-garbage-collection-and-more","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-1830-filestream-storage-garbage-collection-and-more\/","title":{"rendered":"A SQL Server DBA myth a day: (18\/30) FILESTREAM storage, garbage collection, and more"},"content":{"rendered":"

(Look in the Misconceptions<\/a> 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<\/a>, plus my online Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n

\nPhew – starting to get tiring having to make sure I get out a blog post in the morning before doing everything else in the day in case I forget… another multi-mythbusting post for you today!<\/font>\n<\/p>\n

\nMyth #18:<\/strong> various FILESTREAM data myths<\/em>.<\/font><\/font>\n<\/p>\n

\nAll of them are FALSE<\/em><\/u><\/strong><\/font>\n<\/p>\n

\n18a)<\/strong> FILESTREAM data can be stored remotely<\/em> <\/font>\n<\/p>\n

\nNo. A FILESTREAM data container (the invented name for the NTFS directory structure that stores the FILESTREAM data) must adhere to the same locality rules as regular database data and log files – i.e. it must be placed on storage 'local' to the Windows server running SQL Server. FILESTREAM data can be *accessed* using a UNC path, as long as the client has contacted the local SQL Server and obtained the necessary transaction context to use when opening the FILESTREAM file.<\/font>\n<\/p>\n

\n18b)<\/strong> FILESTREAM data containers can be nested<\/em><\/font>\n<\/p>\n

\nNo. Two FILESTREAM data containers for the same database may share a root directory, but data containers cannot be nested, and data containers from different databases cannot share a directory. I blogged an example script that shows this at Misconceptions around FILESTREAM storage<\/a>.<\/font>\n<\/p>\n

\n18c)<\/strong> Partial updates to FILESTREAM files are supported<\/em><\/font>\n<\/p>\n

\nNo. Any update to a FILESTREAM file creates an entirely new FILESTREAM file, which will be picked up by log backups. This is why FILESTREAM cannot be used with database mirroring – the amount of data to be pushed to the mirror would be prohibitive. Hopefully a future version of SQL Server will implement a differencing mechanism that will allow partial updates and hence database mirroring compatibility.<\/font>\n<\/p>\n

\n18d)<\/strong> FILESTREAM garbage collection occurs instantaneously<\/em><\/font>\n<\/p>\n

\nNo. Garbage collection occurs once a FILESTREAM file is no longer required (usually meaning it's been backed up by a log backup) AND a further checkpoint occurs. This is very non-intuitive and leads many people to think that FILESTREAM garbage collection isn't working for them. I explained this in detail in my post FILESTREAM garbage collection<\/a>.<\/font>\n<\/p>\n

\n18e)<\/strong> FILESTREAM directory and filenames cannot be determined<\/em><\/font>\n<\/p>\n

\nNo. There is method to the seeming madness of the GUIDs and weird filenames. The actual FILESTREAM filenames are the character representation of the LSN of the log record that described the creation of the file. The table and column directory names are GUIDs that you can get from system tables. (To be entirely accurate, the table directories are actually 'rowset' directories as far as the Storage Engine is concerned – with a rowset being equal to the portion of a table in a single partition of a partitioned tabled).<\/font>\n<\/p>\n

\nI have two blog posts which crack open the various names and system tables and show you how to figure out what's what:<\/font>\n<\/p>\n