kiranghag
asked on
Some drives not seen by SQL Server
This is my first question in this area...not interacted with database much before this so consider me naive.
The server is a Dell PowerEdge server with two physical drives (configured as raid 0) and two SAN drives.
local drive was partitioned into C, E and F
the san drives were added as G and H
drive D is CDROM
Operating system is Windows2000 Advanced server
and Database is SQL Server 2000
One of the database was stored (MDF and LDF files) on SAN drives G and H
yesterday all of a sudden the databse was taken into suspect mode and user logins were automatically disabled (maybe due to that).
while investigating more, we found that the SQL server is not detecting both san partitions (G,H) and also local parition E.
first we thought it was a san issue, but san drives and all other drives can be accessed by windows explorer in read write mode.
i found this in the event log
The MSSQLServerADHelper service terminated with service-specific error 3221225572.
but after the SQL server was reinstalled by the admin, this service itself has gone.
all the paritions and drives are shown healthy and active in the disk manager
now what could make SQL server not to see all of them...
The server is a Dell PowerEdge server with two physical drives (configured as raid 0) and two SAN drives.
local drive was partitioned into C, E and F
the san drives were added as G and H
drive D is CDROM
Operating system is Windows2000 Advanced server
and Database is SQL Server 2000
One of the database was stored (MDF and LDF files) on SAN drives G and H
yesterday all of a sudden the databse was taken into suspect mode and user logins were automatically disabled (maybe due to that).
while investigating more, we found that the SQL server is not detecting both san partitions (G,H) and also local parition E.
first we thought it was a san issue, but san drives and all other drives can be accessed by windows explorer in read write mode.
i found this in the event log
The MSSQLServerADHelper service terminated with service-specific error 3221225572.
but after the SQL server was reinstalled by the admin, this service itself has gone.
all the paritions and drives are shown healthy and active in the disk manager
now what could make SQL server not to see all of them...
ASKER
>>It is strange that E is not being detected though.
exactly, i am also bugged by this one.
>>SQL won't ever let you do a restore from a non local drive,
I was trying to create a new database, so there it should show all the drives...they were being shown before.
>>Try creating a new database on the SAN and see of you get any errors.
for that it should show the drives for browse...
the san drives previously didnt appear as removable drives, even now...explorer shows them as local disk but sql doesnt see them
exactly, i am also bugged by this one.
>>SQL won't ever let you do a restore from a non local drive,
I was trying to create a new database, so there it should show all the drives...they were being shown before.
>>Try creating a new database on the SAN and see of you get any errors.
for that it should show the drives for browse...
the san drives previously didnt appear as removable drives, even now...explorer shows them as local disk but sql doesnt see them
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>Perhaps try copying a large file between the
i had done that. after the failure occured, since the drives were accessible from explorer, i detached the database from server (which it did without complaining) and then copied the mdf and ldf files onto local partitions. the mdf file is more than 8 gb and it copied well.
>>Log in as your SQL Server service accounts
>>and make sure that they can see the drives.
this is something new to try out...i'll try this today and let you know.if i remember well, the sql service runs using an user who is member of admin group. by default i think it runs with local service account i guess.
You will also need the check this for SQL Server Service.
>>And of course try and work out what is
>>different before and after the issue occured!
this is tricky to find. in fact this is the thing which bugged me more...all of a sudden this problem occurred while no one was working on the server. only doubt i had was on an unexpected server shutdown event that occurred sometime before the problem occurred, but before and after that, the event log seems fine and does not give any clue. but related to this shutdown, i think it occurred due to os problem, mainly because
1. the server's on/off switch is located inside the cover which is locked
2. there are two power supplies, both powered by different power lines, and none of them had reported failure in the log.
anyways...thanks for your help so far..i'll let you know about the observations done today...
i had done that. after the failure occured, since the drives were accessible from explorer, i detached the database from server (which it did without complaining) and then copied the mdf and ldf files onto local partitions. the mdf file is more than 8 gb and it copied well.
>>Log in as your SQL Server service accounts
>>and make sure that they can see the drives.
this is something new to try out...i'll try this today and let you know.if i remember well, the sql service runs using an user who is member of admin group. by default i think it runs with local service account i guess.
You will also need the check this for SQL Server Service.
>>And of course try and work out what is
>>different before and after the issue occured!
this is tricky to find. in fact this is the thing which bugged me more...all of a sudden this problem occurred while no one was working on the server. only doubt i had was on an unexpected server shutdown event that occurred sometime before the problem occurred, but before and after that, the event log seems fine and does not give any clue. but related to this shutdown, i think it occurred due to os problem, mainly because
1. the server's on/off switch is located inside the cover which is locked
2. there are two power supplies, both powered by different power lines, and none of them had reported failure in the log.
anyways...thanks for your help so far..i'll let you know about the observations done today...
ASKER
>>Log in as your SQL Server service accounts
>>and make sure that they can see the drives.
i checked the account, it was local system account
then i wondered how i could log in using that...
after that i went in disk administrator, there i tried to see what is different in the properties of the paritions...
i could see that the drives which were visible to SQL had everyone set to full control
whereas the invisible drives had administrator set to full access only.
i set all the drives to everyone full access and VOILLAA...i could get back all the drives....
now my concern is whether setting all the paritions with everyone full conrol cause any security problem later on...
does it mean that my server drives are running less secure...
>>and make sure that they can see the drives.
i checked the account, it was local system account
then i wondered how i could log in using that...
after that i went in disk administrator, there i tried to see what is different in the properties of the paritions...
i could see that the drives which were visible to SQL had everyone set to full control
whereas the invisible drives had administrator set to full access only.
i set all the drives to everyone full access and VOILLAA...i could get back all the drives....
now my concern is whether setting all the paritions with everyone full conrol cause any security problem later on...
does it mean that my server drives are running less secure...
You should probably create another user especially for the SQL services.
Add the SQL User to the domain administrators group, change the services in SQL to use the SQL user, remove everyone full access from these volumes.
That will let you restrict access to the drives but still allow SQL to get access.
You should also then reduce the privileges of the SQL user so it isn't a power user, it just has sufficent access to the resources it needs.
Add the SQL User to the domain administrators group, change the services in SQL to use the SQL user, remove everyone full access from these volumes.
That will let you restrict access to the drives but still allow SQL to get access.
You should also then reduce the privileges of the SQL user so it isn't a power user, it just has sufficent access to the resources it needs.
ASKER
thanks, with that tip, I was able to solve the problem....
now i can see all the drives and i am using local system account for all the drives and sql service...
now i can see all the drives and i am using local system account for all the drives and sql service...
Try creating a new database on the SAN and see of you get any errors.
It is strange that E is not being detected though.