SQL 2K EE SP4 on Windows 2k3 servers, here it is the errors found from errorlog when opening by text file. DB services and SQLAgent services are both on. when using EM to view, Database and Management folds showed no items. 2007-05-11 14:31:10.42 spid3 LogWriter: Operating system error 21(The device is not ready.) encountered. 2007-05-11 14:31:10.42 spid3 Write error during log flush. Shutting down server 2007-05-11 14:31:30.42 spid52 Error: 9001, Severity: 21, State: 1 2007-05-11 14:31:30.42 spid52 The log for database 'tempdb' is not available.. 2007-05-11 14:31:50.52 spid52 Error: 9001, Severity: 21, State: 1 2007-05-11 14:31:50.52 spid52 The log for database 'tempdb' is not available.. 2007-05-11 14:32:10.53 spid52 Error: 9001, Severity: 21, State: 1 2007-05-11 14:32:10.53 spid52 The log for database 'tempdb' is not available.. 2007-05-11 14:32:30.55 spid52 Error: 9001, Severity: 21, State: 1 2007-05-11 14:32:30.55 spid52 The log for database 'tempdb' is not available.. 2007-05-11 14:32:50.56 spid52 Error: 9001, Severity: 21, State: 1 2007-05-11 14:32:50.56 spid52 The log for database 'tempdb' is not available.. ------------------ Bug explorer/finder/seeker/locator ------------------
it seems all the drives have enough disk space ( more than 40GB free space) ------------------ Bug explorer/finder/seeker/locator ------------------
My another server had the similar problem. 2007-05-09 11:48:11.84 spid1 SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [g:mssqldata emdb.ldf] in database [tempdb] (2). The OS file handle is 0x000004D0. The offset of the latest long IO is: 0x00000001af7c00 2007-05-09 11:48:22.64 spid2 LogWriter: Operating system error 1167(The device is not connected.) encountered. 2007-05-09 11:48:22.64 spid2 Write error during log flush. Shutting down server 2007-05-09 11:48:28.42 spid54 Error: 9001, Severity: 21, State: 1 2007-05-09 11:48:28.42 spid54 The log for database 'tempdb' is not available.. 2007-05-09 11:48:44.59 logon Login succeeded for user 'TEST'. Connection: Trusted. 2007-05-09 11:48:48.62 spid54 Error: 9001, Severity: 21, State: 1 2007-05-09 11:48:48.62 spid54 The log for database 'tempdb' is not available.. 2007-05-09 11:48:52.66 logon Login succeeded for user 'TEST'. Connection: Trusted. ------------------ Bug explorer/finder/seeker/locator ------------------
Looks like there is a Hard Ware/access related issue, make sure all disk are available and sql can access them... Error clearly stats that log file is not available for tempdb means you are totally done.... Also take a look to the following article...... http://support.microsoft.com/default.aspx?scid=kb;EN-US;838765 MohammedU. Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Even though the drive has enough space the processess demanding more log space on TEMPDB and amount of time SQL is taking to increase the size is the main issue. So you have to limit the number of processess or perform in smaller batches to ensure the TEMPDB log can cope up the pressure, it is also a good thing to configure the TEMPDB accordingly. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
