Attached DBs coming online as READ-ONLY - SS2K on Server 2003 R2

[JayKusch]

Had a server that went belly up. ALL database files were on the SAN and were "supposedly" ok.

We rebuilt the server to have Windows Advanced Server 2003 R2. Install went fine.

Installed SQL Server 2000 SP4 + latest builds to 2187 and SQL Server 2005 SP1.
That all went well.

We attached a few of our DBs in SS2K and ALL of them are coming up in read only mode. We try to put then in normal mode my unchecking the read only option on the properties of the DB but we get the following error:

ERROR 5105: Device activation error. The physical file named 'D:\myDB.mdf' may be incorrect.
Device activation error. The physical file named 'D:\myDB.ldf' may be incorrect.
Could not restart database 'myDB'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file named 'D:\myDB.ldf' may be incorrect.
sp_dboption failed.

The D: drive in this example is a LOCAL drive. I moved this DB from the SAN. The MDF was on on volume and the LDF was on another volume.

I did receieve the same error when the database files were in the SAN locations.

I thought by moving both the MDF and LDF to a local drive I could elimitate the possibility that the SAN was causing the issue BUT that seems not to be the case.

The account being used for the SQL Server services is a Domain Admin account that has also been added to the Admins group on the server.

All persmission are set and look good on the shares and volumes.

As a side note, I copied over a small test DB from another server (copied the MDF & LDF).

I attached the DB and IT TOO attached in a READ-ONLY mode.

Thanks ALL!


Thanks

J. Kusch

 

[SQLBill]

NOOOOOOOOOOOOOOOOOOOOO! I had a similar problem. It works like this....imagine the SANS drives are built into the server. You install Windows 2000. It formats all the drives to NTFS (or whatever) and then installs the OS. When you install another Windows version, it has to format all the drives again to recognize the OS. I was told it puts a marker or something on the drive. This is all good when all the drives are being formatted. But you have removed drives (the SANS). Those are formatted for Windows 2000 and the rest of the computer is formatted for Windows 2003. They are not compatible.

The solution we had to follow....install the original OS back on the computer. Attach the SAN and make sure everything works. Then upgrade everything to the new OS.

-SQLBill

Posting advice: FAQ481-4875

 

[JayKusch]

Thank Bill!!!

But it was a bit weirder than that! We found the answer after a couple of days on the phone and finally contacting the SAN support folk.

Damn thing was all caused by a faulty SCSI card - DOH!!!

I am by means NO Systems Engineer BUT from what I was relayed here is what happened:

The throughput of the connection to the SAN is usually running at 320 mbits. We found that our throughput was at 80 mbits, only 75% of the throughput the "system" knows it should see.

Now I am told applications query this throughput metric the System has stored away and expects data to be pulled/pushed at a rate in ms that corresponds to the last know throughput speed. So let’s say for 320 mbits response time should be .001 ms or so with some acceptable deviation.

Of course the ms number I threw out is made up (I will see if I can get the real mbits/ms ratios later).

SO ... when SQL Server was given the command to attach the DB files which resided on the SAN ... the throughput speed was not what SQL Server was expecting and it took longer to attach the files and in some manner put the DBs in READ_ONLY mode.

In saying this though ... I still have one question my SE is tracking down and one question I have M$ chewing on and they are:

1. Since the locals drives (C: & D ARE NOT on the SCSI
controller and cable ... why did the DBs I attached
from those drives also come up READ_ONLY

2. FOR M$: If this is indeed a SCSI controller/cable
issue ... what caused the attached DBs to go into
READ_ONLY mode.

Aside from that ... the DBs are now attaching and are online and are not READ_ONLY!

GO FIGURE!

I will post updates as I gather more pertinent details.

Thanks ALL!


Thanks

J. Kusch