Urgent help for tempdb

  • Hi all,

    In one of my production server faces a problem of one issue.

    Here is the issue

    The log file for database tempdb is full. Back up the transaction log for the database to free up some log space.

    according to my exp, 1) restart the server

    2) shrinking the log

    but thses 2 methods require downtime. without downtime any other ways

    I already check the log file size -------> almost full

    but free space in mdf file

  • Your message is a bit confusing. You can’t backup the tempdb’s log, and there is no reason to do so. TempDB’s recovery model is simple and the server clears the log records that are not needed automatically.

    Between the 2 ways that you wrote about (Shrinking the log and restarting the server), restarting the server causes downtime. Shrinking the log doesn’t cause downtime, but if the error message is that you don’t have free space in the log, then I don’t see how shrinking it will give you more space. I also wouldn’t restart the server. Although restarting the server makes many problems disappear, it is not a magic solution for all problems. I would try to increase the log’s size, and would also try to check what process is using the tempDB

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for ur reply

    what u said is good for me, any other ways

  • DBCC SHRINKDATABASE

    Visit this link

    http://support.microsoft.com/kb/307487

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Add another file to the log on tempdb on a different drive. Then you should get a log truncation at the next checkpoint and then you can shrink the file in place. It might take a checkpoint or two until the log clears enough for shrinking. I'd figure out what filled the log on tempdb, because it will happen again.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Syed Jahanzaib Bin hassan (6/30/2011)


    DBCC SHRINKDATABASE

    Visit this link

    http://support.microsoft.com/kb/307487%5B/quote%5D

    don't listen to him ^

    What Mr. Fritchey suggests is best if your problem is that you are running out of disk space on the disk which the log file resides.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • If you still have enough space on the disk where the log file of the tempdb is located, I will make the log file bigger(Adi's suggestion), but if that disk does not have enough free space I will advice you to follow Grant's instructions. Good luck!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply