I'm using FILESTREAM, but am having an issue with garbage collection. The FILESTREAM files are not being cleaned up when I set the column to null.
In the event log, there is an enormous number of this error:
Internal FILESTREAM error: failed to access the garbage collection table.
It is error number 5571 on this page: http://msdn.microsoft.com/en-us/library/cc645602.aspx
Using this blog post I've confirmed that a 'tombstone' table exists, but I cannot query it (
Invalid object name 'sys.filestream_tombstone_xxxxxxx'.)
I'm not deleting rows, I'm setting the FILESTREAM column to null, as per this msdn article: "When a FILESTREAM field is set to NULL, the BLOB data associated with the field is deleted."
The database is on a local drive.
The database is using Simple Recovery.
I have executed a number of
CHECKPOINTs when activity was very low. This seems to have done nothing. The error message seems to indicate the FILESTREAM garbage collector is trying to garbage collect, but cannot access the table to find out what to do.
SQL Server 2008 (10.0.2531.0, SP1, Enterprise Edition
There are no other errors preceding the 5571 errors that I can see in the SQL Server Log or Event Log.
There was an error detected. What do I do about this?
DBCC results for 'sys.filestream_tombstone_1819153526'. Msg 8951, Level 16, State 1, Line 1 Table error: table 'sys.filestream_tombstone_1819153526' (ID 1819153526). Data row does not have a matching index row in the index 'FSTSNCIdx' (ID 2). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (4:4712151:3) identified by (oplsn_fseqno = 62856 and oplsn_bOffset = 82647 and oplsn_slotid = 2) with index values 'file_id = 65537 and rowset_guid = '3F972309-9B0B-4C4F-939A-5618897050B4' and column_guid = '4A143C0D-B877-494E-B1E6-B70B0A834BB6' and oplsn_fseqno = 62856 and oplsn_bOffset = 82647 and oplsn_slotid = 2'. There are 209624 rows in 3382 pages for object "sys.filestream_tombstone_1819153526".
How should permissions be set up for FILESTREAM to work? Other people have access to this server, and this database may have been restored from a backup at some point.
How do I check FILESTREAM (Windows) share/container account permissions and account under which SQL Server runs?
Thanks to @vgv8 for prompting for more info and ideas.