After asking this question it is clear to me that I need to be able to perform the garbage collection in the fastest possible time.
How is it possible to tell SQL Server filestream's garbage collector to delete all the files with high priority?
I tried with the CHECKPOINT statement, even by setting a duration (CHECKPOINT 100), but nothing changes.
After deleting 40000 filestream records I see that the garbage collector removes 4-5 files per second. How to tell him "delete them all now"?
I do not have an environment where I can test but have you tried switching to the simple recovery mode?
Unfortunately there's currently no way to force garbage collection (GC) of filestream data. It's handled by an asynchronous background task that only gets invoked every so often and has a limit in the number of files it may process in a single invocation. Other people have already complained about this and Microsoft has promised to address this problem in future releases.
That being said, there are some things you may proactively do to ensure that all deleted files are eligible for garbage collection. A file doesn't automatically become eligible for garbage collection the moment it gets deleted from the database - certain additional conditions have to be met.
The conditions depend on the recovery model of the database, therefore it's important that you know what recovery model your database is in. Note that even if the recovery model (as specified by sys.databases) is full, but you haven't taken a db/log backup since enabling the full recovery model (or since creating the db), the database will behave in many aspects as if it was still in simple recovery model.
Under simple recovery model all that's necessary for a file to be eligible for deletion is that the current checkpoint LSN (the LSN of the last checkpoint) is greater than the LSN of the delete operation that removed the file. Therefore all you can do after you delete the 40,000 rows is to issue a single CHECKPOINT statement and wait.
Things get more complicated when the database is in "truly full" recovery model. If that's the case, then in addition to checkpoint LSN, backup LSN (the LSN of last log backup) must be past the delete LSN. Furthermore, the GC works in 2 phases: on the first pass it only marks a file for deletion but doesn't physically delete it. Only when GC processess the file for the second time will that file get physically deleted from the disk. To make things even more interesting, the first pass of GC "resets" the delete LSN, so the second pass may only process the file when checkpoint LSN and backup LSN are greater than the LSN of the first GC pass.
If you want to know exactly what is going on in the system, you can keep track of current GC progress by looking at a special internal "tombstones" table. Each time a filestream value is deleted from the database, a tombstone is inserted into this table. The tombstone is only removed after the file has been deleted from the disk. The tombstone table's name is sys.filestream_tombstone_ where is some number. You may get the exact name using the following query:
select name from sys.internal_tables where name like '%tombstone%'
Since it's an internal table, to query it you need to log on using DAC (dedicated admin connection).
For example, let's say I've deleted a row with a single filestream value. Now I can see the status of the tombstone by issuing the following query (from DAC):
select * from sys.filestream_tombstone_2073058421
oplsn_fseqno | oplsn_bOffset | oplsn_slotid | file_id | rowset_guid | column_guid | filestream_value_name | transaction_sequence_num |status
31 | 239 | 2 | 65537 | CBA21DD0-C36F-4D19-A59B-F5312712A8F6 | 6D2AA35E-692C-4F7D-8412-94475E76AC25 | 0000001f-000000eb-0002 | 0 | 17
The first 3 fields denote the LSN of the delete operation, but the most important to observe is status. After issuing log backup + checkpoint and letting it run for a few seconds, I query the tombstone table again and I get:
oplsn_fseqno | oplsn_bOffset | oplsn_slotid | file_id | rowset_guid | column_guid | filestream_value_name | transaction_sequence_num |status
31 | 265 | 2 | 65537 | CBA21DD0-C36F-4D19-A59B-F5312712A8F6 | 6D2AA35E-692C-4F7D-8412-94475E76AC25 | 0000001f-000000eb-0002 | 0 | 18
Note that the status has changed (the last 2 bits change from 1 to 2), indicating that the file has been processed by the first GC pass. Additionally, the LSN has been updated with the LSN of the first GC pass, so in order for the second GC pass to be able to ultimately delete the file, we need to bring checkpoint LSN and backup LSN above the new LSN. I issue another checkpoint + log backup, wait a few seconds and re-query the tombstones table. It is now empty and the file has disappeared from the disk.
Keep in mind that there are other things (e.g. replication, other transactions when versioning is enabled) that may prevent particular files from being garbage collected, but in most cases checkpoint and log backup are the 2 major ones.
Oops, I guess I may have gone too deep into the details, but perhaps this will help in some way in understanding the GC behavior.