SQL 2000 Logs disk drive bust

Question

We;ve had a disk go on our SQL server (2000) that contained all the log files, so we replaced the drive (no raid config so data lost) and now when trying to connect to Enterprise manager we cant, just getting the general connection failed message. The service seems to start ok?

Any ideas? I didn't think the log files going would screw it up this much?

Thanks,

John

Answer 1

Are you sure the service started ok? How are you checking?

If the databases were not cleanly shutdown then SQL Server won't automatically recreate log files as there is information in the missing log files that is required to properly run crash recovery on those databases. (See my article in TechNet Magazine Understanding Logging and Recovery in SQL Server).

Do you have backups of the databases? If not you may be looking at having to perform an undocumented and unsupported transaction log rebuild on some of the databases to make them accessible again - which usually leads to corruption (because active transactions didn't get a chance to roll-back properly), which you fix using DBCC CHECKDB.

Thanks

Answer 2

I think there is enough control with the query analyzer to log in and re-tell the database where the log files are. Otherwise, you may have to cycle the SQL server service to re-create the log files and start writing to them again.