4

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:

enter image description here

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

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:

reclaimed_filestream_file_space

5

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)
  • Yes, that was the problem. After following the above steps, the space in the filestream files was released. Awesome, Thanks! – sqlpadawan Jan 27 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.