SQL Server Performance

Troubleshooting problem

Discussion in 'General DBA Questions' started by xiebo2010cx, May 14, 2007.

  1. xiebo2010cx Member


    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
    ------------------
  2. xiebo2010cx Member

    it seems all the drives have enough disk space ( more than 40GB free space)

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  3. xiebo2010cx Member

    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
    ------------------
  4. MohammedU New Member

    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.
  5. satya Moderator

    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.

Share This Page