May 29, 2012 at 4:27 pm
We upgrade our SQL server windows operating system, and then I reattach the mdf files and ldf files.
After attach, those database look fine.
Then later after I restart the server, I try to connect use SQL management studio, then I can only see the database name, the tab cannot be collaped, and not anything showing under the database name.
What could be wrong?
I know our network person also update drivers before I restart the server.
is it some security issue or some other issues?
Thanks
May 29, 2012 at 4:31 pm
it could be security but take a look at the SQL errorlog first via notepad for any error messages
---------------------------------------------------------------------
May 30, 2012 at 2:17 pm
I fix the security by using inherit from the top data and log file security, then the database showed up.
But today I restart the server, it has the issue again, only database name cannot be expanded.
The security is like what I have fixed before, so not sure what to do now and what is wrong.
The error in error log, just shows, the sql service account failed to open explicitly database
May 30, 2012 at 2:21 pm
please post the part of the errorlog with the error messages
---------------------------------------------------------------------
May 30, 2012 at 2:26 pm
Thanks, here is the screen shot of the error log.
May 30, 2012 at 2:42 pm
either F:\mssql\data does not exist or the database files are not in that location, therefore those databases are marked suspect and cannot be opened.
the database files are no longer in the location SQL believes the files to be held in as recorded in the physical_name column of table master.sys.physical_name.
Are the files somewhere else or gone completely?
---------------------------------------------------------------------
May 30, 2012 at 2:50 pm
They are all there.
This is the first time I use detach and attach way to attach database.
Before I used restore database and no problem.
I still think this is related with security, the files was detached by our network admin, because we want to ugprade the server, after he finished install operating sytem, then I tried to attach it, it attaches fine, but when restarted the database cannot expand more,
Now I want to try go back to restore database way instead of detach and attach.
But I would like to figure out what is the problem for security, after detach by some one, then I reattach them, what should I do regarding security
May 30, 2012 at 4:45 pm
are they there in the correct location? That error definitely says the system cannot find the file specified. If it was permissions you would get OS error 5, not 3.
How did the network admin detach the files? How did you attach them?
Restores would work as long as you restore to a location that actually exists.
---------------------------------------------------------------------
May 30, 2012 at 5:47 pm
I restore the database from backup then restart the server, they all look fine in SSMS.
The databases are restored to the same directory as those mdf and ldf files earlier from using attach method.
So I still think it is related with detach and attach permission or security.
I will check also with our network guys to see how he detached the database. For now I only know we want to upgrade the operating system, so he needs to reinstall os, but he said he will make the data file directories drives available to me since they are SAN drives, so I can see the drives and files are still there. I will check with our network people, and update this later.
May 31, 2012 at 8:49 am
My first guess is that they are mapped drives; i.e. the drive letter exists when you are logged in and you attach the files. Then when you log out and back in, it is trying to find the drive letter that does not exist anymore. Better check the SAN setup and make sure that the drive is mounted properly.
Jared
CE - Microsoft
May 31, 2012 at 10:29 am
They are SAN drives, this morning, It happened again. Same error.
I can see the drives and files are there from windows exploer, but database cannot be expanded.
Frustrated, I remember yesterday when I restarted it a couple of times it was OK, (these databases were created from restore, instead of detach attach)
May 31, 2012 at 10:37 am
is that restarting SQL, or restarting the server?
I would take this up with the SAN people, and as jared says, make sure you are not looking at mapped drives rather than actual drives.
---------------------------------------------------------------------
May 31, 2012 at 10:42 am
Before this happened, I restarted SQL service, it looked OK, but by restarting the server itself, then it started to have the problem.
The strange thing is yesterday, after I restored the databases I restarted the server to test - they looked OK, but today it happened again.
(today I made a little change to the owner of database from myself to sa, but I don't think this could cause any problem)
I will check with our SAN person to find out more.
May 31, 2012 at 1:11 pm
We found out the problem is when the SQL service restarted, there is a 30 seconds delay of SAN mounting, so it won't find the path. After I restart the SQL service again after a while , we can see the files.
So now we know the problem, but we don't know how to fix except restart sql service again.
Our network person says the mounted drives are the same as before, no hardware changes, what was changed is new os, new drivers, new sql server verison.
May 31, 2012 at 1:16 pm
Off the top of my head, you can try changing the SQL Services to delayed start. I think there may also be a registry setting or WMI setting to make windows wait for all network dries before starting services... Will look that one up.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply