Error: 9001, Severity: 21, State: 1 | The log for database ‘tempdb’ is not available

Today one of the production servers running SQL Server 2000 was rebooted during maintenance window. All the databases came online after reboot. But few minutes later, the monitoring tool started sending the below alert.

spid51    Error: 9001, Severity: 21, State: 1
spid51    The log for database 'tempdb' is not available

The DBA’s time has come! First headed to the drive on which tempdb transaction log was located to see if it had disappeared. No, that disk was online. Then something was wrong at the instance. To check the databases opened up Query Analyzer and fired sp_helpdb command. Same error. However, tempdb database getting listed under the databases. So issued use tempdb command and it was successful. Then issued sp_helpfile and it listed the data file and the transaction log file as well.

Now everything seems normal still tempdb reports that the log file is missing. Any query which makes tempdb use the transaction log would fail with the same error. It was very clear that SQL Server had issues with the transaction log because of the disk. The entries in the System Event Log at that time confirmed that indeed the storage had some serious issues. Here are some of those.

{Delayed Write Failed} Windows was unable to save all the data for the file . The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere

The driver detected a controller error on \Device\Harddisk0.

The device, \Device\Scsi\vmscsi1, is not ready for access yet.

Since it was maintenance hours, no users were impacted. Since tempdb was having issues, DBA’s task was simplified. As tempdb gets recreated after every services restart, all we had to do was to restart the SQL Services. This time the storage seems have calmed down and the instance as well as the tempdb came online. Most importantly, all the databases continued to be online !

The storage team is already looking into this issue and hopefully they will fix it before this catches on to some other database.