Post reply

Temp db get big in sudden from 200 mb to 9 gb why

  • January 22, 2009 at 10:17 pm

    #383793

    hi, it is strange situation for me i never had this situation before. in sudden tempb in production increased to 9 gb. i was keep watching earlier on the morning when i get off it has increased to 9. we have weekly schedule to shrink all log file. i was working on reporting services which does create to pull data using view and load in table then report use it . reason i did this was view taking more then 30 min to load all data. i don't know if this had something to do. but could not find any reason i did not run profiler either. does anybody has the same situation, resolution,or any suggestion?

    thanks

    sagar

  • January 22, 2009 at 10:29 pm

    #931634

    Hi

    Cant understand your question clearly.

    But here goes.....

    Is the report using lot of temp tables or table data types. Is there any other activity going on like index rebuilds or any such maintenance activity. Any other activity which uses lot of temp tables or table data types.

    U dont have to shrink tempdb log files since tempdb has simple recovery model.

    "Keep Trying"

  • January 22, 2009 at 11:43 pm

    #931645

    Check to see that in temporary tables got created in your temp db in case if you have used temp objects in inserts or updates.This is one such reason i think of immediately.

  • January 23, 2009 at 5:01 am

    #931719

    Hi,

    yes I think so, the reports will be the reason.

    The problem is, that the temp.db is for such things. Big reports need much space and if your memory isn't enougt the temp.db will grow.

    Another situation is when the log is full (maybe there is no disk-space), then the temmp will grow.

    If you want to shrink the temp.db you must restart the SQL Server. But be careful, if the reason is the log, it's better to backup the log first.

    Ralf

  • January 24, 2009 at 11:18 am

    #932249

    thanks, for the reply i am not sure if that is from the report there is no temp table but there are like few table functions inside views which returns millinon row and there are about more then 10 tables join together with functions. does it have any effect to increase temdb because of resource.

    thanks

    sagar

  • January 24, 2009 at 1:09 pm

    #932276

    200 MB is too small for a TempDB, anyway. Shoot... I START my servers with a 9GB TempDB.

    There are several things that can cause TempDB to get so large... reindexing is one and accidental cross joins or triangular joins in code is the other likely candidate.

    --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

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

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