Microsoft SQL 2005 starts then stops with 3414 error

I recently had one of our Windows 2003 server lose it’s disk mirrors and locked up. When it was restarted it has two (virtually idenitical) drives C: and E:. It booted off the primary mirror disk (C:) and all seemed OK except SQL.

I also tried booting off the secondary mirror (E:) but this would not boot (this drive it turns out had some bad blocks).

So I went back to the primary disk. The actual problems was SQL server started but then stopped after a few seconds, the Windows error log showed the unhelpful 3414 error. I google for this, but all that was mentioned was issues with DTC, but this did not relavent as we not use distributed transactions. There was nothing else on the web of note.

I had a look at the MSQL.1\logs directory and this showed problems loading the various databases. So it seems when the disk de-mirrored it was writing SQL transaction logs, and they ended up corrupted. So in my case a generic 3414 error in the error log meant corrupt transactions that could not be rolled forward or back.

More in hope than expectations I tried copying the SQL datafiles and logs back from the faulty secondary drive (E:) and tried to restart SQL and this worked – SQL started without a problem! I was lucky the bad blocks were not near the SQL files. This saved me from having to rebuild the server and restore backups, espcially as some the the DBs were SharePoint, and a SharePoint SQL restore is rarely fun!