{"id":65,"date":"2014-04-11T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/filestream-garbage-collection-with-alwayson-availability-groups"},"modified":"2026-03-18T21:50:56","modified_gmt":"2026-03-18T21:50:56","slug":"filestream-garbage-collection-with-alwayson-availability-groups","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/04\/11\/filestream-garbage-collection-with-alwayson-availability-groups\/","title":{"rendered":"FILESTREAM garbage collection with AlwaysOn Availability Groups"},"content":{"rendered":"

It seems that, of the DBAs who use FILESTREAM, it is common to ask, \u201cwhy do I have more file system objects then I have rows with BLOB columns?\u201d This question inevitably bring us to the topic of the garbage collection process. Paul Randal, as always, did a great job of explaining the garbage collection in his post here<\/a>. The purpose of this post, however, is a bit more specialized. In Paul\u2019s post he explains everything but, when you use Availability Groups<\/a>, it is not clear how the logic relates to the differences induced by database mirroring. We are going to analyze what happens when you add FILESTREAM to a database in an availability group and how this can cause file system bloat and prevent the garbage collection from deleting files that you might think should be getting deleted.<\/p>\n

Garbage collecting<\/h2>\n

To summarize the garbage collecting process; when a CHECKPOINT occurs it will kick off and read a tombstone table to find what files to delete. The ones deleted will only be ones that relate to log sequence numbers (LSN) available to be truncated. Specifically, the VLF that they were written to has to have been marked inactive. You can also kick off the garbage collecting process with\u00a0sp_filestream_force_garbage_collection<\/a>.aspx)\u00a0but it still will only delete files that relate to inactive VLFs.<\/p>\n

Typically the condition of VLFs being inactive would be satisfied by log backups and CHECKPOINTs. When using availability groups, however, you can take as many log backups as you\u2019d like and kick off numerous CHECKPOINTs but still be left with large quantities of files stuck in the tombstone table. This is because of the lazy log truncation but I\u2019m getting ahead of myself, we\u2019ll get to that.<\/p>\n

Demo<\/h2>\n

Establish a database in an availability group<\/h3>\n

Disclaimer: there is an assumption that the reader has a basic understanding of availability groups. This is not intended to be an instructional post on how to setup and configure them. Rather, the script is included as a convenience to anyone who is running this on a development box while following along.<\/em><\/p>\n

USE [master];\n--Create database\nCREATE DATABASE filestream_garbage_collection_test;\nGO\n--Backup to remove it from PSEUDO-SIMPLE recovery.\nBACKUP DATABASE filestream_garbage_collection_test\nTO DISK = '\\\\shareserver\\backups\\filestream_garbage_collection_test_FULL.bak'\nWITH COMPRESSION, STATS=10;\nGO\n--Configure AlwaysOn Availability Group\nCREATE ENDPOINT AG_FS_Endpoint\nAS TCP\n(LISTENER_PORT = 5022)\nFOR DATA_MIRRORING\n(ROLE = ALL,\nENCRYPTION = REQUIRED ALGORITHM AES)\nGO\nALTER ENDPOINT AG_FS_Endpoint STATE = STARTED\nGO\nCREATE LOGIN [DOMAIN\\Acct] FROM WINDOWS\nGRANT CONNECT ON ENDPOINT::AG_FS_Endpoint TO [DOMAIN\\Acct]\nGO\nCREATE AVAILABILITY GROUP [AG_FILESTREAM]\nWITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)\nFOR\nREPLICA ON N'Server' WITH (ENDPOINT_URL = N'TCP:\/\/Server.libtax.com:5022'\n     , FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT\n     , SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50\n     , PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL)\n     , SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));\nGO\n--Wait 5 secs to give the AG time to come online\nWAITFOR DELAY '00:00:05'\nGO\n--Add db to AG\nALTER AVAILABILITY GROUP [AG_FILESTREAM]\n     ADD DATABASE [filestream_garbage_collection_test];\nGO<\/code><\/pre>\n
Setup FILESTREAM objects<\/h3>\n
--Create FILESTREAM FILEGROUP\nALTER DATABASE filestream_garbage_collection_test\n ADD FILEGROUP filestream_filegroup CONTAINS FILESTREAM\nGO\nALTER DATABASE filestream_garbage_collection_test\n ADD FILE\n (\n NAME= 'filestream_filegroup',\n FILENAME = 'M:\\MSSQL11.SITCLDB017D\\MSSQL\\DATA\\filestream.ndf'\n )\n TO FILEGROUP filestream_filegroup\nGO\n--Create FILESTEAM table\nUSE filestream_garbage_collection_test\nGO\nIF EXISTS (SELECT TOP 1 1 FROM sys.tables WHERE name = 'FS_Table')\n DROP TABLE FS_Table;\nGO\nCREATE TABLE FS_Table (ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,\n BLOB VARBINARY(MAX) FILESTREAM NULL);\nGO<\/code><\/pre>\n
At this point we have our test case prepared for data. Before we move forward we need to take a look at DBCC LOGINFO. DBCC LOGINFO will give us a list of all of the VLFs in the transaction log and the status column indicates whether it\u2019s active (status 2) or inactive (status 0). Notice that we have one active VLF and two inactive.<\/p>\n
\"\"<\/a><\/p>\n
Now we will fill up these VLFs with transaction data.<\/p>\n
--INSERT the blobs\nSET NOCOUNT ON\nINSERT INTO FS_Table (ID, BLOB) VALUES\n    (NEWID(),CAST('unnecessary test data 1' AS VARBINARY(MAX))) ;\nGO 40\nINSERT INTO FS_Table (ID, BLOB) VALUES\n    (NEWID(),CAST('unnecessary test data 2' AS VARBINARY(MAX))) ;\nGO 40\nINSERT INTO FS_Table (ID, BLOB) VALUES\n    (NEWID(),CAST('unnecessary test data 3' AS VARBINARY(MAX))) ;\nGO 40\n\n
--Verify data\nUSE filestream_garbage_collection_test\nSELECT ID, CAST(BLOB AS VARCHAR(100)) AS [String_Data]\n    , BLOB, BLOB.PathName() AS [Blob_PathName]\nFROM FS_Table<\/code><\/pre>\n<\/p>\n
We now have 120 records in our table which, if you will check your file system now, will equate to 120 files. Next we need to produce files for the tombstone table. We will UPDATE BLOBs because partial updates of FILESTREAM columns is not supported. SQL Server will create a new file, related to the LSN, and mark the old file for delete. We will also delete BLOB records which will mark those files for deletion.<\/p>\n
--Update some records to create versions for garbage collection\nUPDATE FS_Table\nSET BLOB = CAST('unnecessary test data - updated' AS VARBINARY(MAX))\nWHERE BLOB = CAST('unnecessary test data 1' AS VARBINARY(MAX));\nGO\n\n
--Delete some records to create versions for garbage collection\nDELETE FROM FS_Table\nWHERE BLOB = CAST('unnecessary test data 2' AS VARBINARY(MAX));\nGO<\/p>\n\n
--Verify data\nUSE filestream_garbage_collection_test\nSELECT ID, CAST(BLOB AS VARCHAR(100)) AS [String_Data]\n    , BLOB, BLOB.PathName() AS [Blob_PathName]\nFROM FS_Table<\/code><\/pre>\n<\/p>\n
You can now see that we are left with 80 records in the table. However, there are now 160 files in the file system. Now we want those files to be deleted so we know to see why our VLFs aren\u2019t being cleared.<\/p>\n
DBCC LOGINFO\nSELECT log_reuse_wait_desc, name\nFROM sys.databases\nWHERE name = DB_NAME()<\/code><\/pre>\n
\"\"<\/a><\/p>\n
Our first three VLFs are now active and we\u2019ve gained one more VLF via auto growth. We also see that we need a log backup to clear the VLFs. So, let\u2019s do that.<\/p>\n
--Backup log in attempt to mark VLFs inactive.\nBACKUP LOG filestream_garbage_collection_test\n TO DISK = '\\\\share\\backups\\filestream_garbage_collection_test_LOG_1.trn'\n WITH COMPRESSION;\n\n
DBCC LOGINFO\nSELECT log_reuse_wait_desc, name\nFROM sys.databases\nWHERE name = DB_NAME()<\/code><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Our log_reuse_wait_desc is NOTHING but our VLFs have not converted their status from 2 (active) to 0 (inactive). We know we need the VLFs to be inactive but maybe we can force things with another log backup followed by a forced call to the FILSTREAM garbage collector.<\/p>\n
--Backup log in attempt to mark VLFs inactive.\nBACKUP LOG filestream_garbage_collection_test\n TO DISK = '\\\\share\\backups\\filestream_garbage_collection_test_LOG_2.trn'\n WITH COMPRESSION;\n\n
--Force FS garbage collection\nCHECKPOINT\nEXEC sp_filestream_force_garbage_collection\n    @dbname = N'filestream_garbage_collection_test';<\/code><\/pre>\n<\/p>\n
Even after all of that, when checking the file system, you will still see 160 files instead of the intended 80. In addition, you\u2019ll notice that the\u00a0sp_filestream_force_garbage_collection output showed counts in the num_marked_for_collection_items and\/or num_unprocessed_items. This is a representation of items in the tombstone table that have not been deleted. To wrap up the demo we will now remove the database from the availability group and induce another call to the garbage collection.<\/p>\n
--Drop AG\nUSE [master]\nGO\nDROP AVAILABILITY GROUP [AG_FILESTREAM];\nGO\n\n
DBCC LOGINFO\nSELECT log_reuse_wait_desc, name\nFROM sys.databases\nWHERE name = DB_NAME()<\/p>\n\n
--Backup log in attempt to mark VLFs inactive.\nBACKUP LOG filestream_garbage_collection_test\n TO DISK = '\\\\share\\backups\\filestream_garbage_collection_test_LOG_3.trn'\n WITH COMPRESSION;<\/p>\n\n
--Force FS garbage collection\nCHECKPOINT\nEXEC sp_filestream_force_garbage_collection\n    @dbname = N'filestream_garbage_collection_test';<\/code><\/pre>\n<\/p>\n
Now that we\u2019ve removed the availability group from the equation we are back to the expected one VLF marked as active and, after checking the file system, we have the expected 80 files to relate to our 80 existing BLOB records.<\/p>\n
\"\"<\/a><\/p>\n
Conclusion<\/h2>\n
When you have a database in an availability group (AG) the lazy log truncator takes effect. That lazy log truncator will only mark a VLF inactive (ready for truncation\/clearing) when that VLF is needed for transactional processing. This means that once you\u2019ve used all of your VLFs, they will all stay in the active status for as long as you are in an AG. As the transation log round-robins the VLFs it will clear one VLF at a time as needed. This is a feature intended to aid in synchronizing new replicas added to the AG. The more data in the log, the wider your window for synchronization is.<\/p>\n
Due to this behavior and the logic in the garbage collection for FILESTREAM, your tombstone files will not be deleted until the VLF that they were written to is cleared by the round-robin affect of log writing. This can be a big problem if you do what one development team I know did, which was to create a Storage database and put all their BLOBs there because they wanted it segregated from their primary transactional database. Since FILESTREAM only logs the non-BLOB data this made their log filled very slowly and large numbers of transactions would have to go by before they would move on to a different VLF and, in affect, clear a single VLF. This left them in a situation where their file system was three times larger than the number of BLOBs in their database which was wasting hundreds of GBs of space.<\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
It seems that, of the DBAs who use FILESTREAM, it is common to ask, \u201cwhy do I have more file system objects then I have rows with BLOB columns?\u201d This question inevitably bring us to the topic of the garbage collection process. Paul Randal, as always, did a great job of explaining the garbage collection […]<\/p>\n","protected":false},"author":1,"featured_media":526,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-65","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/65","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=65"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/65\/revisions"}],"predecessor-version":[{"id":328,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/65\/revisions\/328"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/526"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}