Post reply

Tempdb growing huge

  • July 20, 2010 at 2:10 am

    #94988

    i am working with sql server 2000 & few days ago i found my tempdb database become so large. it was nearly 200MB but within 12 hours in became 15 GB!

    >>My tempdb’s current properties are

    a. Recovery model : Simple

    b. Size: 14016 MB, Space available: 14015 MB

    c. Automatically grow file & Unrestricted file growth

    d. File growth rate: 10%

    it should mentioned that no of select/insert/update or delete query not increase in my database within that period so it seems quite unusual to me…

    Whatever I found some solution from net and one of them I chose following solution:

    -------------------------------------------

    ALTER DATABASE tempdb MODIFY FILE

    (NAME = 'tempdev', SIZE = target_size_in_MB)

    --Deisred target size for the data file

    ALTER DATABASE tempdb MODIFY FILE

    (NAME = 'templog', SIZE = target_size_in_MB)

    --Desired target size for the log file

    Stop and start SQL SERVER....

    ----------------------------------------------------

    My questions are:

    01. if I manually resize tempdb.mdf and tempdb.ldf file

    Will it lose any data from tempdb?

    02. Will it affect any on my user database?

    I am very confused about that issue, if anyone has better solution please advice me.

    Thanking you

    Mainur Rahman Shohan

  • July 20, 2010 at 9:03 am

    #1195597

    Here is the thing, if you do not find out why the TempDb is growing, then what ever you do now, you will end up with the same situation again. The TempDB can grow when large due to one bad query (select) that was run or it could be a bulk update or bulk insert. If that is the case you have to make sure that the transaction is complete before you do anything.

    You have your recovery model as simple. Keep in mind that you are restricting yourself with no DR strategy for this DB.

    Like the name implies, TempDB is just Temporary. You will not loose data in your main DB. (Make sure what ever transaction caused this growth is complete)

    -Roy

  • July 20, 2010 at 4:48 pm

    #1195875

    I agree with Roy. You have to find out what the cause of the growth is before you do anything else.

    As a side bar, a 15GB TempDB may not be all that outlandish depending on what you're doing and how big tables and indexes are.

    --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)
    Intro to Tally Tables and Functions

  • July 20, 2010 at 9:46 pm

    #1195925

    Is there potentially any database maintenance causing this (using the tempdb for sorting etc)?

  • July 20, 2010 at 11:15 pm

    #1195948

    As Roy and Jeff have said, find what is causing the growth to occur.

    Here is an article discussing how to do that.

    http://qa.sqlservercentral.com/articles/Log+growth/69476/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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