question

chrisrdba avatar image
0 Votes"
chrisrdba asked chrisrdba commented

Question on FILESTREAM integrity issue.

Greetings. I've got an integrity check that blows up for the reasons listed here.



I took the same actions listed for 9 files in non-prod, and now the integrity check works w/o issue.

Does anyone know of a good reason to not do this?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered chrisrdba commented

I think the proper way would be to a point-in-restore of the filegroup. That would bring back the original files. Then again, given that they are infected, maybe you don't want them back.

Now you still have a corrupt database in some meaning, although SQL Server does really notice. But if you would modify these files with an UPDATE, and you would later restore a backup from before the AV incident and apply logs, you might get a mess, because this will restore the files that were never updated.

Then again, you could argue that this is largely academic.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you mean a point-in-time restore?

This has been happening for like a year and a half (long story), we're way past that point.

0 Votes 0 ·

LOL! That was not one of my best ideas. :-)

I think you can try it - but restore a backup to a test environment before you do it production.

Hm, I wonder happens if you simply delete the documents in the table? Does it scream blue murder when the files are missing? (Again, try in test first.)

0 Votes 0 ·

Assuming you mean try manually adding the files (?), yes I've done a test in non-prod and after adding 8 or 9 files I'm able to do a DBCC CHECKDB w/o any error messages.

I can't fathom that this would cause any issues, but this is obviously a pretty rare thing to do.

0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered chrisrdba commented

Hi @chrisrdba,

From the link which you shared here, the Poster got an error after DBCC CHECKDB. If any errors are reported by DBCC CHECKDB, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. If no backup exists, running repair corrects the errors reported. The repair option to use is specified at the end of the list of reported errors. However, correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require deleting some pages, and therefore some data. Please refer to DBCC CHECKDB (Transact-SQL) to get more details.

Does anyone know of a good reason to not do this?

Do you mean the reason not to do DBCC CHECKDB? I suggest you do DBCC CHECKDB regularly. DBCC CHECKDB warns you of corruption so that you can fix it before it gets too bad. But if you have a very large database, running a full DBCC CHECKDB will take some or more time. You can refer to CHECKDB (Part 6): Consistency checking options for a VLDB to get more information about the plan for consistency checking options for very large databases.

If I misunderstand your questions, please let me know and describe your question more clearly. Thank you.


Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm not reading that he still had errors after he implemented his fix:

VIDGuide
System Admin
5 years ago
Well did it and it seems happy now. A full dbcc is scheduled for this weekend, we will see what the outcome is, but basic tests so far suggest that using dummy files has resolved it :)

Also, no I don't mean a reason to not run DBCC's -- I run them regularly and that's how I know about this issue. I mean a reason to not implement the fix the poster did, and I did in my non-prod env.

IMO adding files seems like a no brainer compared to ALLOW_DATA_LOSS, but I don't know what I don't know. Can this cause other issues?

Thanks!

0 Votes 0 ·

We recommend that implement the fix if you have an error message after DBCC CHECKDB, like the poster. Restore backups is one way. Of course, please make sure there has no virus in the backup files and do it at non-product environment first. Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option is not an alternative for restoring from a known good backup. It is an emergency "last resort" option recommended for use only if restoring from a backup is not possible.

0 Votes 0 ·

Hi, did any answers could help you? If there has an answer helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

I just another question of Erland -- not ready to close

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered chrisrdba commented

How does one tie the user data to the documents?


I guess you that your question is "how do I find the rows to which the missing document belongs".

I will to confess that I don't know, and I don't really have the time to research for the next few days.

One idea is to loop over the table and for every row select datalength(filestreamcol) before you restore the files and trap any errors. Not that I know if you get errors - maybe you only get NULL back. While you could do this in T-SQL, it may be better to do it in PowerShell, because the you get may be vile and terminate the session.

Then again, what if you do this after you have added the files back? You could insert simple text files with a known message.

Then again, it should be able to use the ROWGUIDCOL in the filestream table, to map the file, but you would need to poke around to figure out how that mapping is.

If you have a multiple tables with FILESTREAM column it gets worse, since you would need to know which table. Or try all of them.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ugg, thanks

0 Votes 0 ·