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...
LVL 10
kiranghagAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
SQL won't ever let you do a restore from a non local drive, so if these drives are missing when you go to restore, this is normal operation.

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.
0
kiranghagAuthor Commented:
>>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

0
nmcdermaidCommented:
Perhaps try copying a large file between the local machine and the SAN drive. If there is an intermittent problem, the large file copy will fail, and you know it is an issue with the SAN.

Log in as your SQL Server service accounts and make sure that they can see the drives. I don't have SQL Server installed so i can't run you through it, but you should be able to right click on the server registration and check which user the services are using from the tabs there.

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!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

kiranghagAuthor Commented:
>>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...
0
kiranghagAuthor Commented:
>>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...
0
nmcdermaidCommented:
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.

0
kiranghagAuthor Commented:
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...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.