Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What is the precise version number of SQL Server (do you have all the latest patches applied)? Same goes for Windows Servers itself. Are there any errors in the SQL Server error log? Also, the Windows Event Viewer might have error messages. Add all this info to your question, please! –  Max Vernon Oct 31 '13 at 16:14
    
What do you mean by "far too slow". - how long? How many files are there? –  Max Vernon Oct 31 '13 at 16:16
    
@MaxVernon, there are over 60k files, so I've stopped the query after about 10 minutes. I will gather the other info you requested and add it to the question; I have not observed any errors, but I've been working on this frantically so I might have missed something. –  dansan Oct 31 '13 at 16:19
    
Are these 60,000 files all in the same folder? –  Max Vernon Oct 31 '13 at 16:20
1  
@MaxVernon The 60,000 files are spread out amongst four top-level folders and lots of subfolders. –  dansan Oct 31 '13 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.