{"id":927,"date":"2009-03-11T15:50:00","date_gmt":"2009-03-11T15:50:00","guid":{"rendered":"\/blogs\/paul\/post\/FILESTREAM-directory-structure.aspx"},"modified":"2017-06-22T05:49:50","modified_gmt":"2017-06-22T12:49:50","slug":"filestream-directory-structure","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/","title":{"rendered":"FILESTREAM directory structure"},"content":{"rendered":"<p style=\"text-align: justify;\">After writing the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/hh461480.aspx\">FILESTREAM whitepaper<\/a> for Microsoft, I&#8217;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.<\/p>\n<p>When you want to use FILESTREAM data, you first add a filegroup (during or after database creation):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER DATABASE &#x5B;FileStreamTestDB] ADD FILEGROUP &#x5B;FileStreamGroup1] CONTAINS FILESTREAM;\r\nGO\r\n<\/pre>\n<p>And then add a &#8216;file&#8217; to the filegroup:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER DATABASE &#x5B;FileStreamTestDB] ADD FILE (\r\nNAME = &#x5B;FSGroup1File], FILENAME = N'C:\\Metro Labs\\FileStreamTestDB\\Documents')\r\nTO FILEGROUP &#x5B;FileStreamGroup1];\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">The &#8216;file&#8217; is actually the pathname to what will become the root directory of the FILESTREAM data container. When it&#8217;s initially created, it will contain a single file, filestream.hdr, and a single directory <em>$FSLOG<\/em>. <em>Filestream.hdr<\/em> is a metadata file describing the data container and the <em>$FSLOG<\/em> directory is the FILESTREAM equivalent of the database transaction log. You can think of them as equivalent, although the FILESTREAM log has some interesting semantics.<\/p>\n<p style=\"text-align: justify;\">The question I most often get is: are all the FILESTREAM files for a database stored in one gigantic directory? The answer is no.<\/p>\n<p style=\"text-align: justify;\">The root directory of the data container contains one sub-directory for each table (or each partition of a partitioned table). Each of those directories contains a further sub-directory for each FILESTREAM column defined in the table. An example is below, with the screen shot taken after running the following code:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE TABLE &#x5B;FileStreamTest1] (\r\n    &#x5B;DocId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,\r\n    &#x5B;DocName] VARCHAR (25),\r\n    &#x5B;Document] VARBINARY(MAX) FILESTREAM);\r\nGO\r\n\r\nCREATE TABLE &#x5B;FileStreamTest2] (\r\n    &#x5B;DocId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,\r\n    &#x5B;DocName] VARCHAR (25),\r\n    &#x5B;Document1] VARBINARY(MAX) FILESTREAM,\r\n    &#x5B;Document2] VARBINARY(MAX) FILESTREAM);\r\nGO\r\n\r\nINSERT INTO &#x5B;FileStreamTest1] VALUES (NEWID (), 'Paul Randal', CAST ('SQLskills.com' AS VARBINARY(MAX)));\r\nINSERT INTO &#x5B;FileStreamTest1] VALUES (NEWID (), 'Kimberly Tripp', CAST ('SQLskills.com' AS VARBINARY(MAX)));\r\nGO\r\n<\/pre>\n<p><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg\" alt=\"\" width=\"800\" height=\"600\" \/><\/p>\n<p style=\"text-align: justify;\">This image shows\u00a0the FILESTREAM data container for\u00a0our database that has two tables with FILESTREAM columns, each with a single partition. The first table has a\u00a0two FILESTREAM columns and the second has a single FILESTREAM column. The filenames of all these directories are GUIDs and I explain how to match directories to tables and columns in <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure-where-do-the-guids-come-from\/\" target=\"_blank\" rel=\"noopener noreferrer\">this post<\/a>.<\/p>\n<p style=\"text-align: justify;\">In the example, you can see two FILESTREAM files in a column-level directory. The FILESTREAM file names are actually the log-sequence number from the database transaction log at the time the files were created. You can correlate these by looking at the data with <em>DBCC PAGE<\/em>, but first finding the allocated pages using <em>sp_AllocationMetadata<\/em> (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\">this blog post<\/a>):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nEXEC sp_AllocationMetadata FileStreamTest1;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nObject Name     Index ID Alloc Unit ID     Alloc Unit Type   First Page Root Page First IAM Page\r\n--------------- -------- ----------------- ---------------   ---------- --------- --------------\r\nFileStreamTest1 0        72057594039697408 IN_ROW_DATA       (1:169)    (0:0)     (1:170)\r\nFileStreamTest1 0        72057594039762944 ROW_OVERFLOW_DATA (0:0)      (0:0)     (0:0)\r\nFileStreamTest1 2        72057594039828480 IN_ROW_DATA       (1:171)    (1:171)   (1:172)\r\n<\/pre>\n<p style=\"text-align: justify;\">Notice there&#8217;s a nonclustered index as well as the heap &#8211; that&#8217;s the index that&#8217;s enforcing the uniqueness constraint on the <em>UNIQUEIDENTIFIER<\/em> column. Now we can use <em>DBCC PAGE<\/em> to look at the first page of the heap, which will have out data records in:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC TRACEON (3604);\r\nDBCC PAGE (FileStreamTestDB, 1, 169, 3);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n(snip)\r\n\r\nSlot 0 Offset 0x60 Length 88\r\n\r\nRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 88\r\nMemory Dump @0x514EC060\r\n\r\n00000000:   30001400 140d5047 2ca9f24f 874d35ca \u20200.....PG,\u00a9\u00f2O\u0087M5\u00ca\r\n00000010:   e9e77649 03000002 00280058 80506175 \u2020\u00e9\u00e7vI.....(.X.Pau\r\n00000020:   6c205261 6e64616c 03000000 00000080 \u2020l Randal........\r\n00000030:   140d5047 2ca9f24f 874d35ca e9e77649 \u2020..PG,\u00a9\u00f2O\u0087M5\u00ca\u00e9\u00e7vI\r\n00000040:   01000000 68020000 00000000 17000000 \u2020....h...........\r\n00000050:   79000000 0c000000 \u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020y.......\r\n\r\nSlot 0 Column 1 Offset 0x4 Length 16 Length (physical) 16\r\n\r\nDocId = 47500d14-a92c-4ff2-874d-35cae9e77649\r\n\r\nSlot 0 Column 2 Offset 0x1d Length 11 Length (physical) 11\r\n\r\nDocName = Paul Randal\r\n\r\nDocument = &#x5B;Filestream column] Slot 0 Column 3 Offset 0x28 Length 48\r\n\r\nColType = 3                          FileId = -2147483648                 UpdateSeq = 1\r\nCreateLSN = 00000017:00000079:000c (23:121:12)                            TxFMiniVer = 0\r\nXdesId = (0:616)\r\n\r\n(snip)\r\n<\/pre>\n<p>You can see that the <em>CreateLSN<\/em> above matches the filename of the first FIL<em>ESTREAM file in the example image.<\/em><\/p>\n<p><em>Hopefully this explains how the FILESTREAM files are stored &#8211; more on this<\/em> in the next post where I&#8217;ll explain how updates and garbage collection are implemented.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>After writing the FILESTREAM whitepaper for Microsoft, I&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,41,48,62,86],"tags":[],"class_list":["post-927","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-filestream","category-inside-the-storage-engine","category-on-disk-structures","category-sql-server-2008"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>FILESTREAM directory structure - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"FILESTREAM directory structure - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"After writing the FILESTREAM whitepaper for Microsoft, I&#8217;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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-11T15:50:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-06-22T12:49:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/\",\"name\":\"FILESTREAM directory structure - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg\",\"datePublished\":\"2009-03-11T15:50:00+00:00\",\"dateModified\":\"2017-06-22T12:49:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"FILESTREAM directory structure\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"FILESTREAM directory structure - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/","og_locale":"en_US","og_type":"article","og_title":"FILESTREAM directory structure - Paul S. Randal","og_description":"After writing the FILESTREAM whitepaper for Microsoft, I&#8217;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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/","og_site_name":"Paul S. Randal","article_published_time":"2009-03-11T15:50:00+00:00","article_modified_time":"2017-06-22T12:49:50+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/","name":"FILESTREAM directory structure - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg","datePublished":"2009-03-11T15:50:00+00:00","dateModified":"2017-06-22T12:49:50+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/fsdirstructure2.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/filestream-directory-structure\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"FILESTREAM directory structure"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/927","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=927"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/927\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=927"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=927"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=927"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}