locked
The log for database 'tempdb' is not available. RRS feed

  • Question

  • The log for database 'tempdb' is not available. 

    I am getting this error very frequently,

    i restart the sqlserver and it start working and after two or three days the same error generates.

    can any one point me out why the same is happening.

    • Moved by Naomi N Thursday, July 12, 2012 3:10 AM Better answer can be here (From:Transact-SQL)
    Thursday, July 12, 2012 3:01 AM

Answers

  • I changed the TEMPDB file size to 1GB and growth is 10%

    Now its running fine let i will post agains if the problem is still happening.


    Ranvir Sharma for Pratibha Total IT Solutions SMTPSERVER.in (EMPowering Business Relationship) 9555687676, 8826323688

    • Marked as answer by Ranvir Sharma Thursday, July 12, 2012 8:43 AM
    Thursday, July 12, 2012 8:43 AM

All replies

  • Hi,

    Which version of SQL plus SP and CU are you using? Did this just start happening were there any changes to your disk config, etc? Run the below code and try to browse to the path for the logfile and check that it exists and is accessible. This code should display details for each data file and log file for tempdb make sure you are using only 1 log file there is no performance benefit from having multiple log files but it is ok to have multiple data files.

    use tempdb
    go
    sp_helpfile
    go


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Thursday, July 12, 2012 4:41 AM
  • http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/7d9e7bfd-d897-402f-aec7-f6e5b97db74b

    I just gone through

    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

    use tempdb
    go
    sp_helpfile
    go

    and changed the tempdp path to c:\sqldatabase

    and now waiting to see when next my server fire the same error

    and restart the SQL Services for this to take effect.


    Ranvir Sharma for Pratibha Total IT Solutions SMTPSERVER.in (EMPowering Business Relationship) 9555687676, 8826323688

    Thursday, July 12, 2012 5:35 AM
  • Thursday, July 12, 2012 5:48 AM
    Answerer
  • check the size of the tempdb using

    sp_helpdb

    log file size of  tempdb

    DBCC SQLPERF(lOGSPACE)

    check for the auto growth option of the tempdb, if the tempdb  datbase is size is limited ,it gives your, change your auth growth optio of the database


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, July 12, 2012 7:16 AM
  • I changed the TEMPDB file size to 1GB and growth is 10%

    Now its running fine let i will post agains if the problem is still happening.


    Ranvir Sharma for Pratibha Total IT Solutions SMTPSERVER.in (EMPowering Business Relationship) 9555687676, 8826323688

    • Marked as answer by Ranvir Sharma Thursday, July 12, 2012 8:43 AM
    Thursday, July 12, 2012 8:43 AM