Post reply

high consumption for tempdb

  • September 17, 2008 at 8:21 am

    #123572

    Hi,

    I have a high consumption of tempdb.

    I have 54 databases.

    I only need know how to fix that problem, but i dont know... could you guys help me with that problem?? You know what is the specifc database have a bigest consumption of that 54 databases?

    For the report of sqlserver, only tells me when you're running, I check by period. Is Possibility?

    Thanks and kind regards.

    []'s

    Douglas

    []'s
    Douglas R. Oliveira

  • September 17, 2008 at 8:18 pm

    #873352

    Ummm... what are you calling "high consumption"? How big is the MDF and the LDF file for TempDB right now?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 17, 2008 at 10:42 pm

    #873380

    What do you mean by high consumption, are you facing any problem due to tempdb full.

    If your tempdb data file or log file is full and it can't grow further, Please have a look.

    1. Find long running transactions

    2. Find is there any big temporary tables are creating by sp or any user

    3. Find any uncommited transactions or rollback

    4. Find at what time your tempdb is getting full like at particular time or at always

    5. Any dbcc commands running ...

    Please find the articel below

    http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

    http://support.microsoft.com/default.aspx/kb/256650

    http://support.microsoft.com/default.aspx/kb/272318

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

    Rajesh Kasturi

  • October 1, 2008 at 6:02 am

    #879410

    is a big LDF file.

    []'s

    Douglas

    []'s
    Douglas R. Oliveira

  • October 1, 2008 at 6:36 am

    #879428

    LDF is the logfile. A large LDF file doesn't mean it's using all the spacelog, probably there's a lot of free space in this LDF file. To check this, run:

    dbcc sqlperf(logspace)

    Maybe you ran a big batch/update/reorg which blew up the tempdb?

    To solve this safely, restart SQL server (which re-creates the Tempdb). You could also shrink the logfile by either T-SQL or Management Studio

    Wilfred
    The best things in life are the simple things

  • October 1, 2008 at 6:49 am

    #879435

    douglasr (9/17/2008)

    I have a high consumption of tempdb.

    This is not a problem, this is just a sympthom showing how your code works. Look at the code.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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