Many times, people have asked me to share my experiences as a freelance consultant. This is one of the experiences I would like to share. Sometimes, I have been in a situation where I have to ask a restart of SQL Server. The situation becomes stressful when the server is restarted, and production database fails to come online. In this blog, we would discuss error 5511 for filestream which I encountered after restarting SQL Server service.
We restarted the SQL Server service and one of the databases fails to come online and we see the error: 5511. We have even restarted the server, but it does not seem to help them. Here are the messages in SQL Server ERRORLOG.
The error was referring to a FileStream Corrupt file — ‘GUID.txt’ under log folder ‘\\?\J:\Blobstore_SQLAuthority\$FSLOG’
I searched on the internet and found — https://blogs.msdn.microsoft.com/psssql/2011/06/23/how-it-works-filestream-rsfx-garbage-collection/
As per article: In the file stream container directory tree is the $FSLOG directory with a set of files as well. The file names have embedded information and are considered part of the database log chain. Part of the file name is an LSN that matches the LSN file name of the actual file storage. The File stream garbage collector uses these files to help determine the cleanup requirements.
DANGER: Deleting a file directly from the file stream container is considered database corruption and dbcc checkdb will report corruption errors. These files are linked to the database in a transactional way and the deletion of a file is the same as a damaged page within the database.
In our case, we just restart SQL Service and there was no hardware failure. So, I thought of taking a risk and we moved the corrupted file to a different folder and then tried to bring the database online. The database came online. In the above blog, it is said that we should not directly delete files from the $FSLOG folder but we have done it and it has worked. I did not know how this has affected the database itself and the application. We ran CheckDB and that came out clean. My client also monitored the server for a couple weeks and updated me that everything seems to work fine.
Have you seen such issues?
Reference: Pinal Dave (https://blog.sqlauthority.com)