none
[298] SQLServer Error: 9001, The log for database 'tempdb' is not available.

    Question

  •  

    I have SQL server 2000 standard edition running on Windows 2003 server. I am getting this error

    [298] SQLServer Error: 9001, The log for database 'tempdb' is not available. [SQLSTATE HY000] (ConnIsLoginSysAdmin)

    I have enough space and tempdb log file set  on unrestricted groeth. I don't know why I am getting this error

    Wednesday, May 14, 2008 5:32 PM

All replies

  • Run the following:

     

    Code Snippet

    use tempdb
    go
    sp_helpfile
    go

     

     

     

    Look at the path for the log file, and then navigate to that path to see if it exists.  If the path is invalid, then you can fix it with the following commands:

     

    Code Snippet

    use master

    go

    alter database tempdb

    modify file(name=tempdev,filename='d:\databases\tempdb.mdf')

    go

    alter database tempdb

    modify file(name=templog,filename='d:\database_logs\templog.ldf')

    go

     

     

    You have to restart the SQL Services for this to take effect.
    • Proposed as answer by Ranvir Sharma Thursday, July 12, 2012 5:37 AM
    Thursday, May 15, 2008 12:43 AM
    Moderator
  • One problem the OP may have is that he may not be able to get Query Analyzer to connect to the SQL Instance in order to run a query.  The SQL Error log should have the path information to where the log file for TempDB is supposed to be.

     

    I would check that, and confirm that the drive and directory are available, then try restarting the SQL Server service (which recreates TempDB).

    Thursday, May 15, 2008 1:55 AM
    Moderator
  • Thank you, Jonathan.

    I could run a backup successfully after changing the tempdb file.

     

    I ran the backup in 2008 Enterprise. But the questions I have are first, I didn't restart the service and ran the backup and it succeeded.

     

    Second, after the backup succeeded I checked if the tempdb file name changed. But it is still same. The file name before change was 'tempdb'. After I ran your second code the tempdb file was same as 'tempdb'.I expected to see 'tempdb.ldf'.

    What happened?

     

     

    Wednesday, September 8, 2010 7:42 PM
  • you should see two files tempdb.mdf and templog.ldf.

    Secondly , i think you gave the same path as it was before ...so the path in the metadata got corrected .it would have given you this messages after changing the path :

    The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

    But as I said you would have changed the path to the same location where tempdb is already existing .

    For example if my tempdb files are located at : c:\temp\tempdb.mdf and D:\temp\templog.ldf and I run the alter database tempdb modify file command keeping the paths same as c:\temp\tempdb.mdf and D:\temp\templog.ldf ....then a restart is not required as the files were existing , its only that the path in the metadata that was suppose to be corrected ....

    You can correct me If I am not ....


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by JSQL DBA Monday, September 13, 2010 4:15 PM
    Sunday, September 12, 2010 12:46 PM
  • I got below message.

    The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

    You are right. I changed the path to the old path


     


    Monday, September 13, 2010 3:27 PM
  • Thanks a ton for Jonathan, Well i had issues in SQLServer Error: 9001, The log for database 'tempdb' is not available and In SQL Enterprise Manager under database tree shows " No ITEMS found" but was able to see all databases in query analyzer. Both got resolved after executinng the above code snipet and restarting  the services.
    Wednesday, January 12, 2011 8:20 PM
  • Great ....

    It solves the problem in my case also, in my case the data file and log file all were ok. I have just restarted the SQL server service and it worked after that, but I am surprised why it happened. If you can share your valuable idea which may help me to take the preventive actions....

    Cheers....

    Anoarul.....

    Wednesday, January 8, 2014 3:58 AM
  • Hi - Error 9001 is output of previous Error As per MSDN Error 9001 generate below message

    Message - The log for database '%.*ls' is not available. Check the event log for related error messages. Resolve any errors and restart the database

    Explanation - The database log was taken offline. Usually this signifies a catastrophic failure that requires the database to restart (Workaround)

    User Action - Diagnose other errors and restart the instance of SQL Server if it has not already restarted itself.

    But i Feel read SQL error log & Event viewer with timestamp you will definatly find root cause of the issue like as below http://connect.microsoft.com/SQLServer/feedback/details/686313/deadlock-with-system-process-causes-the-log-for-database-xxx-is-not-available-error


    SanjayTiwari

    Tuesday, May 13, 2014 10:31 PM