Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Some drives not seen by SQL Server

Posted on 2003-10-27
7
Medium Priority
?
748 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 1000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

824 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