How do I reclaim space in filestream data files for database in a SQL Server Availability Groups

Question

I have a SQL 2016 Ent. Edition AG with 3 nodes. We have a database in the AG with 5 filestream tables. Each table is in it's own filestream data file. Today I fixed a bug where all of the files were being saved to one filestream data file by rebuilding the indexes.

In dev, we don't have an AG and the database is in simple recovery. The space was reclaimed. The before and after looks like this:

When I ran the same code in the AG, all of the nodes look like this:

The files were moved to the right filestream data file, but space wasn't recovered from the original data file.

At first I thought it was garage collection, I read Paul Randal's blog post. I shrunk the logfile, then created a junk table, added a ton of rows via an explicit trans, ran a log backup and checkpoint, all on the primary node. The log file did grow, the previously active VLF was marked inactive.

To complicate matters, backups are full copy-only \ log backups on a secondary node.

What's the correct way to reclaim the space in this scenario?

Edit: After following Andy's steps in his blog, space was reclaimed. Each AG node looks like:

Answer 1

I believe you are experiencing a sightly different scenario related to the bug described here, and officially reported here¹.

When a filestream table is dropped (or in your case rebuilt & moved), garbage collection will not clean up if the database is in an AG.

To work around the bug, you would need to:

• Remove the database from the AG
• take a log backup
• manually run garbage collection
• apply log backup(s) to secondary replicas WITH NORECOVERY
• rejoin the database to the AG (by applying log backups, you can avoid a full resync)

---

^{1 - link is dead and Wayback Machine has only a blank page, at http://web.archive.org/web/20210506131855/https://feedback.azure.com/forums/908035-sql-server/suggestions/32905099/}