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:
Best Answer
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:
WITH NORECOVERY