shrink filestream filegroup

Marco Dell'Oca 141 Reputation points
2022-10-31T15:49:20.567+00:00

I need to shrink the dimensions of the filestream filegroup in my sql server 2016.
I think that the filestrem filegroup is empty because all the tables containing blob are empty.
How can I do?

Thanks

Marco Dell'Oca

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,138 questions
{count} votes

Accepted answer
  1. Seeya Xi-MSFT 16,456 Reputation points
    2022-11-01T07:18:40.26+00:00

    Hi @Marco Dell'Oca ,

    Welcome to Microsoft Q&A!
    It’s the Garbage Collector process in the background which removed the files from the OS when the records if deleted from the table. Please refer to this blog: FILESTREAM garbage collection. Here are some suggestions:

    1. If there is not much of activity on a regular basis, then you can run manual checkpoint in the database.
    2. Regular transaction log backups are needed, in case of FULL and BULK_LOGGED recovery model.
    3. Make sure there is not a long running transaction in the database.
      If both of the above are taken care and still free space is running lower than we can use sp_filestream_force_garbage_collection to force the garbage clean up manually.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 104.3K Reputation points MVP
    2022-10-31T21:51:11.563+00:00

    Are there any actual files in the folder for Filestream?

    If you recently have deleted all data, you still need to take log backups for the files to be garbage collected. I have a recollection that you need to take two backups for some reason.

    0 comments No comments

  2. Marco Dell'Oca 141 Reputation points
    2022-11-04T18:01:19.74+00:00

    Thanks for the replies.
    I noticed that the filetable containing the filestreams has zeroed in size after a couple of backups.
    Thank you
    Marco Dell'Oca

    0 comments No comments