September 10, 2014 at 12:43 pm
I’ve run DBCC CHECKDB on multiple servers and this one is a first for me. It's also the first I'm using a DB with Filestream in use. I get the following error when I try to run the job and it hits the specific database. I"m using the Ola.Hallegren Maintenance Solution, but this isn't an issue with the solution. I've run the command on it's own:
DBCC CHECKDB('FCA') WITH all_errormsgs, no_infomsgs, data_purity
Results:
Msg 7905, Level 16, State 1, Line 1
Database error: The directory "temp" is not a valid FILESTREAM directory.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'FCA'.
Ola was kind enough to reply to my email query and suggest I ask in the MSDN forums. I figured I'd start here.
Thanks for any help.
kDBAjan
September 10, 2014 at 2:00 pm
Just to add some more info, of which I find this curious. The following is the result of the DBCC DBINFO tableresults showing the LastKnownGood:
ParentObjectObjectFieldVALUE
DBINFO STRUCTURE:DBINFO @0x0000000012BBD820dbi_dbccLastKnownGood2014-09-10 14:21:02.300
So I have the error on the job, consistently, but DBCC is logging an updated LastKnownGood whenever I run the CHECKDB. If it is an error in the FILESTREAM, I've no idea how to go about this one.
Thanks again.
kDBAjan
September 12, 2014 at 12:59 pm
Man
I'd hope this one wouldn't be a stumper. I pulled some more detail that I can share that goes along with the error, from the Server logs:
DBCC CHECKDB (FCA) WITH all_errormsgs, no_infomsgs, data_purity executed by me\kDBAjan found 1 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 5 seconds. Internal database snapshot has split point LSN = 0000093b:00000182:0001 and first LSN = 0000093b:00000181:0001
September 29, 2014 at 10:07 am
Well, if anyone is interested, I solved this problem 2 different ways. Let me just preface this with one detail. The server I was having the issue on was secured in such a way that I did not have access to it properly (ie. no RDP, shares were secured so I couldn't drill down).
(Spoiler alert... Method 2 was much easier).
Both methods were done AFTER taking a fresh backup, of course.
Method 1
After asking the SQL community, researching and seeing answers to other questions that if there is a DBCC error prepare to repair the data from backups and logs or a new structure if no good backups exist, I got started. I had to go the new structure route since I did not have a good backup (knowingly) since the condition existed before I started at my new job a couple months ago. After asking a couple of other well known SQL gurus out there, I got the reply to look at getting a Microsoft ticket started. Trying to avoid $$ signs was looking unlikely.
So I scripted the entire database structure (which thankfully was not overly large on tables) and created a new database, leaving the existing one intact. Then I imported the data from the existing database into the new one, 1 table at a time (with the expectation of having an error).
Thankfully no errors occurred while importing (which was interesting) and once all was done, and DBCC done again.... no more errors!!
Intermission
So, I took the task to another server, where I had more access. For this server, I could RDP to it, see all drive and directory information. While just looking at the problem database on this server, I noticed this (see attachment for actual screenshot).
NameSizeTypeDate Modified
$FSLOGFile Folder9/28/2014
5f1b7b51-5168...File Folder5/8/2012
f475d0a5-4b9d...File Folder5/8/2012
[highlight]temp[/highlight]File Folder11/29/2012
filestream.hdr1 KBHDR File9/28/2014
Apparently this temp folder was created in the filestream repository nearly 2 years ago. However, what it did for me was allow me to actually see the folder name "temp" matching my original error from DBCC. With this new information, I went back to my network system admins to have them look at the file space on the server I'd just fixed. Sure enough, no "temp" directory there.
So I knew I had a solution (Method 1) but what if, just perhaps, the answer was simpler. Of course done when no one was in the database.
Method 2
Navigate to the directory containing the Filestream. Expanding into the filestream I could see the "temp" folder and the fact that it was empty. Click on "temp" and delete. Fast, simple, done.
Reran DBCC just as before and voila.... "Command(s) completed successfully."
Summary
Apparently someone, not knowing what the filestream folder/object was, created a "temp" folder in it on that fateful day some nearly 2 years ago. No knowledge of why as any involved left before I took this new job. However, I am suggesting that if you run into this error and you have a recently taken backup, it's possible this might fix your error, if you're lucky enough to run into this as your only DBCC issue.
Hope that was thorough enough.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply