Solved

SQL Server 2012 and FileStream Errors

Posted on 2014-03-18
2
716 Views
Last Modified: 2014-03-19
Long story short, for some reason, my FileStream database got corrupted.  We're using it to search Resume documents.  The files in the FileStream Share were being deleted in a Command Window using the DEL command, then XCOPY was used to copy fresh Resume documents from our main File Server each night.  Apparently that's a bad idea because the database ended up hosed and the Log file was 95GB.  I finally got the Log file down to 1GB after setting Recovery Mode to Simple but now I need to fix the errors.

DBCC CHECKTABLE ("RESUMES.dbo.ftbl_resume_files") is reporting the following:
Msg 7908, Level 16, State 1, Line 1
Table error: The file "\de483992-99d3-479a-89ae-b6d15faaaa6f\000078c1-00" in the rowset directory ID a0798d74-a878-4551-9b81-acd807075528 is not a valid FILESTREAM file in container ID 65537.
Msg 7908, Level 16, State 1, Line 1
Table error: The file "\de483992-99d3-479a-89ae-b6d15faaaa6f\New Text Document.txt" in the rowset directory ID a0798d74-a878-4551-9b81-acd807075528 is not a valid FILESTREAM file in container ID 65537.

Open in new window

I used DELETE FROM RESUMES.dbo.ftbl_resume_files to get rid of all document files via SQL but it's still reporting these errors.  I want to basically flush out and reset the FileStream tables back to an empty state so I can copy new Resume Documents back over and create a procedure to properly clear it out each night.  Any ideas would be appreciated.
0
Comment
Question by:wchestnut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39939931
IF and ONLY IF you indeed do not care about data loss then you can try to run a

DBCC CHECKTABLE ("RESUMES.dbo.ftbl_resume_files", REPAIR_ALLOW_DATA_LOSS);  

but make sure nothing is touching that table while DBCC is running

OR...

You can even try to DROP the table and re-create it IF and ONLY IF you do NOT need any of that table data.


Also I suggest use TRUNCATE TABLE instead of DELETE FROM table if you need to get rid of all data in it.
0
 

Author Closing Comment

by:wchestnut
ID: 39940088
Thanks for the idea, lcohan.  No, I don't care about the data so I ran your first option and hit this: The system cannot self repair this error.  I believe this happened because the REPAIR_ALLOW_DATA_LOSS does not work for FileStream tables.

So, I use the Script Table as -> Create to and created a query to drop then re-create the table for future use.  DBCC CHECKDB came out clean.

I will use the TRUNCATE TABLE from now on.  Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question