none
Sql Server Filestream garbage collection error

    Question

  • 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 sql server 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
    The reason for the error is not at all clear.

    I need to reclaim the space used by the filestream files. So far, I've found that the tombstone table index contains an error (using DBCC CHECKDB), but I'm not sure it's related. 

    Please see here for the things I've tried so far:
    http://stackoverflow.com/questions/4383387/sql-filestream-garbage-collection-issue
    Monday, December 13, 2010 6:36 AM

Answers

  • We dont support modifications to system tables. It you are unable to open a case with CSS, then I would suggest creating a new database and importing all the
     content in the new database and monitoring if the issue re-occurs. If it does, then definitely you need CSS assistance to figure out the root-cause for this issue as this would be out of scope on a MSDN thread due to the data collection that might be required.

    If you are able to open a support incident, then
    we could collect the necessary information and check if anything can be done at this point based on what we see in the data collected. Since there are inconsistencies in the FS system table, there would be failures
    during cleanup.

    From what you are mentioning above, they look to be orphaned entries but not sure how they got there in the first place.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Tuesday, December 14, 2010 5:45 PM

All replies

  • Were there any disk or operating system related issues on the server? If yes, then you would need to correct the environment/disk/OS related issues first and then start with a restored database backup that doesn't have the inconsistencies on the system tables or create a new database with the same skeleton and import the data from the older one.

    Since there are consistency errors reported for the tombstone table which is critical for performing cleanup, the FS cleanup operation might fail which I suspect is happening in your case.

    And has this issue happened before or can you reproduce this at will? If the answer is Yes for either of the two, then would it be possible for you to open a case with CSS for us to investigate this issue further?


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Monday, December 13, 2010 5:22 PM
  •  

    Hi Amit,

    I do not know of any OS or disk problems (I inherited this server, so there may have been problems I do not know about).

    I'm not sure when this issue started, as we have only recently begun setting FS columns to  NULL - we have never done this before.

    I cannot reproduce this - our test and development SQL Servers perform as expected. The test server uses an older backup of the live database, so this error was not always present.

    I have used Process Monitor to track sqlservr.exe actions. This may be of interest.

    At times corresponding to the log entries of "Internal FILESTREAM error: failed to access the garbage collection table.", 'sqlservr.exe' tries to find four filestream files (see examples below).

    None of the four files actually exist in the folder (checked using 'dir'). Could this be fixed by removing the entries from the tombstone table?

    Thanks

    Eg1.

    Log Name:      Application

    Source:        MSSQLSERVER

    Date:          14/12/2010 10:22:27 AM

    Event ID:      5571

    Task Category: Server

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      xxxxxxxxxxxx

    Description:

    Internal FILESTREAM error: failed to access the garbage collection table.

     

    10:22:27.6135125 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f587-0000d708-0023 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

    10:22:27.6142857 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f588-00010eda-0003 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

    10:22:27.6148674 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f588-00014139-0003 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

    10:22:27.6153207 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f588-00014171-0003 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

     

    Eg2.

    Log Name:      Application

    Source:        MSSQLSERVER

    Date:          14/12/2010 10:21:37 AM

    Event ID:      5571

    Task Category: Server

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      xxxxxxxxxxxx

    Description:

    Internal FILESTREAM error: failed to access the garbage collection table.

     

    10:21:37.6105344 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f587-0000d708-0023 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

    10:21:37.6110731 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f588-00010eda-0003 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

    10:21:37.6114781 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f588-00014139-0003 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

    10:21:37.6118937 AM sqlservr.exe 1716 CreateFile G:\Database\xxxx\3f972309-9b0b-4c4f-939a-5618897050b4\4a143c0d-b877-494e-b1e6-b70b0a834bb6\0000f588-00014171-0003 NAME NOT FOUND Desired Access: Read Attributes, Delete, Synchronize, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Delete On Close, Attributes: N, ShareMode: Read, Write, Delete, AllocationSize: n/a

     

     

    Tuesday, December 14, 2010 12:42 AM
  • We dont support modifications to system tables. It you are unable to open a case with CSS, then I would suggest creating a new database and importing all the
     content in the new database and monitoring if the issue re-occurs. If it does, then definitely you need CSS assistance to figure out the root-cause for this issue as this would be out of scope on a MSDN thread due to the data collection that might be required.

    If you are able to open a support incident, then
    we could collect the necessary information and check if anything can be done at this point based on what we see in the data collected. Since there are inconsistencies in the FS system table, there would be failures
    during cleanup.

    From what you are mentioning above, they look to be orphaned entries but not sure how they got there in the first place.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Tuesday, December 14, 2010 5:45 PM