June 9, 2017 at 7:23 am
Hello --
We are using Ola Hallengren's MaintenanceSolution scripts on our server. The Database Integrity Check for the user databases has failed, and I have traced the cause to the following:
The FILESTREAM of the database in question was originally on the M:\ volume of the server, and it was recently moved to the I: volume.
Will disabling, and re-enabling the Data Integrity Check job correct this problem, or should I stop and restart the SQL Agent?
Thanks.
June 9, 2017 at 8:03 am
Probably neither, as checkDB failures are not related to the job, nor to SQL Agent.
What errors are you getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 8:17 am
Hello --
Thank-you for your reply. The errors that I am seeing are the following:
Date and time: 2017-06-09 06:06:36
Command: DBCC CHECKDB ([RayStationPatientDB_502_Q1_2017]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Msg 3634, Level 16, State 1, Server MGHROSTOR3\RAYCLINICAL, Line 1
The operating system returned the error '3(The system cannot find the path specified.)' while attempting 'FindFirstFile' on 'M:\DATA\RayStationPatientDB_502_Q1_2017\027177da-68e1-4207-87b9-f747d82ab74d\*'.
Table error: The FILESTREAM directory ID b100f060-3c34-483d-a72d-f9c8d61a88ac for column ID 8 of object ID 94623380, index ID 1, partition ID 72057594048086016 was not found.
Msg 7934, Level 16, State 1, Server MGHROSTOR3\RAYCLINICAL, Line 1
CHECKDB found 1 allocation errors and 2 consistency errors in table 'RS_RoiVolumeDistribution_405' (object ID 1893581784).
CHECKDB found 6 allocation errors and 10 consistency errors in database 'RayStationPatientDB_502_Q1_2017'.
Outcome: Failed
Duration: 00:00:44
Date and time: 2017-06-09 06:07:20
June 9, 2017 at 8:36 am
How did you move the filestream data from M drive to I?
What happens if you try to query some of the filestream data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 8:55 am
Hello --
The filestream was moved via the following steps:
1. The database was backed up, and subsequently deleted within the Studio utility.
2. The database was restored with the volume changed to the new drive letter.
The database is currently in use, and there have not been any reports of problems accessing the data.
June 9, 2017 at 9:18 am
Can you please post the CREATE DATABASE statement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 12:50 pm
My apologies for asking this: What log file should I reference to get the command syntax requested in your posting?
Thank-you.
June 9, 2017 at 1:06 pm
No log file. I want you to script and post the CREATE DATABASE statement for the database in question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2017 at 1:22 pm
If I understand the problem correctly, you have filestream access turned on pointing to M: but you removed the M drive.
Did you re-point the filestream access to I:? Are you even using filestream? If not, you could just disable it.
I am fairly confident that filestream access is stored at the database level, but moving the database files will not repoint the filestream to a different drive. You would need to do that yourself.
This is why Gail was asking for the CREATE DATABASE script as that should contain information about where the filestream is pointing and I suspect that it is pointing to the M: drive still.
The steps you listed for moving the database would not have moved the filestream folder path.
You also didn't answer Gail's question of "what happens when you try to query some of the filestream data?". You indicate there are no problems accessing the data, but I presume you mean the data in the database not the data in the filestream; those are 2 completely different things.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 9, 2017 at 1:35 pm
Hello --
When I went through the motions to restore the database I made sure to go to the Files page, and change the FileStream location to the new volume prior to running the actual restore. We are using the Filestream as the application utilizes it. I have confirmed the Filestream configuration is correct through the Studio, and also the Explorer utility.
June 11, 2017 at 11:44 am
Hello --
I created a plan via the Maintenance Wizard that would do an integrity check to the database in question. When I ran this particular check, it completed successfully.
Why would there be a discrepancy?
Thanks.
June 12, 2017 at 1:36 am
There wouldn't be, they do exactly the same thing.
Double-check what databases the checks were run on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2017 at 6:37 am
Hello --
I double-checked the database that was the target for the integrity check, and it is the one that is the subject of this discussion.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply