Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Issues with TempDB ever increasing RE: Issues with TempDB ever increasing

  • January 13, 2014 at 2:30 pm

    #1679726

    stormsentinelcammy (1/13/2014)

    Hi,

    I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then slowly without stopping. I've run many queries to figure out what is running, below is the result of the query below which actually gave me the results I could use. As can be seen they are all internal spid's is there any way to find out why tempdb continues to grow out of control and how to mitigate it?

    --Query that gave the result set

    SELECT session_id

    ,SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count

    ,SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count

    FROM sys.dm_db_task_space_usage

    GROUP BY session_id

    HAVING SUM(internal_objects_alloc_page_count) > 0

    I can't put my finger on it but I remember something about a fault with SQL Server 2005 where if you had certain intial settings, TempDB would run away like this. I know that's not much help but it may help you with a Google search or may jog someone's memory that actually has a URL for this fault.

    What is the Service Pack level of your 2005 installation?

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