Solved

Some drives not seen by SQL Server

Posted on 2003-10-27
7
737 Views
Last Modified: 2012-08-14
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...
0
Comment
Question by:kiranghag
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9632537
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
 
LVL 10

Author Comment

by:kiranghag
ID: 9632904
>>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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 250 total points
ID: 9633053
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Author Comment

by:kiranghag
ID: 9638543
>>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
 
LVL 10

Author Comment

by:kiranghag
ID: 9639211
>>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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9640252
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
 
LVL 10

Author Comment

by:kiranghag
ID: 9647387
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now