Post reply

Urgent help for tempdb

  • June 30, 2011 at 12:47 am

    #253649

    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

  • June 30, 2011 at 1:09 am

    #1346328

    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/

  • June 30, 2011 at 1:19 am

    #1346333

    Thanks for ur reply

    what u said is good for me, any other ways

  • June 30, 2011 at 1:26 am

    #1346339

    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

  • June 30, 2011 at 6:45 am

    #1346496

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 30, 2011 at 6:55 am

    #1346509

    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.

  • June 30, 2011 at 8:00 am

    #1346549

    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!



    My blog

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

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