We're running an implementation of SQL Server 2012 FileTables with several scientific instruments with PCs that write data directly to a mapped drive of a FileTables share. The size of this share is now about 60GB.
Twice in the past six months, I've discovered backups failing. I've only been able to solve this by running chkdsk /f with the volume dismounted.
After running chkdsk this last time, we started seeing a large number of files showing up as having 0 bytes when we downloaded them. However, in the File Explorer (and in the cached_file_size column in SQL Server) the file still has size, yet the file_stream column shows 0x.
To add to the crazy, passing the 0x file_stream column to the DATALENGTH() returns the cached_file_length.
I'm at a loss to how to proceed -- I need to restore these files, but I can't even find them efficiently because the DATALENGTH() function is lying.
So the questions:
How would you go about finding these files efficiently?
SELECT stream_id FROM dbo.FileTable WHERE file_stream = 0x
is far too slow.
How would you attempt to troubleshoot the issues described above?
EDIT SQL Server version 11.0.3128 SQL Error logs look normal. Windows error logs are reporting problems like the following:
The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'FsDoHandler::CreateFile' on 'h:_production_db\UploadFiles\264759d7-159c-4dbb-b8b4-a52b2ab06153\be030672-3a28-4067-a621-4e1f653ced40\000ce69b-00000801-0030' at 'fstrman.cpp'(2075).
The error that causes the backups to fail (and for me to run CHKDSK) is:
The operating system returned the error '1392(The file or directory is corrupted and unreadable.)' while attempting 'FindNextFile' on 'h:_production_db\UploadFiles\264759d7-159c-4dbb-b8b4-a52b2ab06153\be030672-3a28-4067-a621-4e1f653ced40'.
Thanks for help! Dan.
Edit 2: It seems that I can loop through all of the files using .NET to open & close without changing in read-write mode. This seems to update whatever metadata that Windows and SQL Server are using to provide the DATALENGTH and cached_file_size values.
