Help! TempDB just keeps on growing

  • Because of performance reasonse i moved the tempDB to an other disc (which is mucht faster than the old one). After i moved the file the tempDB grew larger and larger to a size of 1 terabyte and when the disc was full some queries dropped because there was no space left for the tempDB.

    This problem started after moving the file to an other drive, does anyone know what can cause this problem? Because now we have to shrink the tempDB manually every day to keep the software running.

  • Try to findout more detail about the processes running on the Tempdb. Find out any open transactions are there or not in Tempdb?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • free_mascot (11/13/2008)


    Try to findout more detail about the processes running on the Tempdb. Find out any open transactions are there or not in Tempdb?

    But in that case you are asuming this is a general problem, which is not cause by the change we made of the location of the TempDB

  • I doubt just changing location of TempDB is acting like this. There must be something beyond this or we are missiong something.

    Have you checked DBCC OPENTRAN ? What is the result?

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • free_mascot (11/13/2008)


    I doubt just changing location of TempDB is acting like this. There must be something beyond this or we are missiong something.

    Have you checked DBCC OPENTRAN ? What is the result?

    There are no open transactions but even with shrinkfile i can't shrink the total TempDB size smaller than 30gyg

  • Guys - I have the same problem. This is not a simple A-B-C back to basics problem. This also happend to me after moving tempdb to another drive. I think we're on to something here. I have been a SQL Server Dba for over 12 years and I've seen nothing like it.

    We have done the usual due diligence of checking everything. If that doesnt convince you, chew on the fact that when the query errors out with the below error, tempdb shows as 99% free !!!

    Could not allocate space for object 'dbo.SORT temporary run storage: 140737593540608' in database 'tempdb' because the 'PRIMARY' filegroup is full.

    I will post updates myself, but would appreciate it if anyone who finds out before I do posts it in this thread.

    Thanks,

    Guru

  • Sander - what is the initial size of your tempdb datafiles? And what is the autogrowth of these files set to?Maybe the size is set to 30GB and that is why you cannot shrink past that?

    Guru - I'm also of the same opinion as free_mascot and don't think that changing the location of tempdb is causing these issues (of course - I could be wrong :).

    A google search yielded this on the same error message: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1113034&SiteID=17

    Is there some heavy activity happening on the DBs (that perhaps would have coincided with the move)?

  • winash (11/13/2008)


    Sander - what is the initial size of your tempdb datafiles? And what is the autogrowth of these files set to?Maybe the size is set to 30GB and that is why you cannot shrink past that?

    initial size is 16gyg (4 files of 4 gyg), autogrowth is set to 512Mb.

  • When you guys made the "new" TempDB, did you make sure it was still set to the "Simple" recovery mode?

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

  • Jeff Moden (11/14/2008)


    When you guys made the "new" TempDB, did you make sure it was still set to the "Simple" recovery mode?

    moving the TempDB files is as simple as altering the file locations and restarting SQL. So i never touched the recovery mode settings. I did double check it right now and it's still Simple.

  • I found this as a possible solution, using shrinkfile, from http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

    [font="Courier New"]use tempdb

    dbcc shrinkfile(templog, 0)

    backup log tempdb with Truncate_Only

    dbcc shrinkfile(templog, 0)

    [/font]

    Avoid the hassle of shrinkdatabase. But maybe have the shrinkfile size larger than zero on the second dbcc shrinkfile.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • :w00t: Just a crazy question, how often do you reboot the box? maintenance or otherwise? I agree with moving the location won't a lot. I had kind of a similar situation with a datamart server. Due to the high number of transaction and processes run by several datamarts the tempdb was almost impossible to handle. We set a job to run and shrink tempdb, but that worked for a while but at the end it didn't.

    Finally we established a reboot routine when the datamarts were not processing anything and tempdb has been behaving normally since (almost 2 years) Just an idea though, every environment is different :w00t:

  • Fernando (11/14/2008)


    :w00t: Just a crazy question, how often do you reboot the box? maintenance or otherwise? I agree with moving the location won't a lot. I had kind of a similar situation with a datamart server. Due to the high number of transaction and processes run by several datamarts the tempdb was almost impossible to handle. We set a job to run and shrink tempdb, but that worked for a while but at the end it didn't.

    Finally we established a reboot routine when the datamarts were not processing anything and tempdb has been behaving normally since (almost 2 years) Just an idea though, every environment is different :w00t:

    Normaly we reboot the machine.... never. But because of the new RAID configuration we rebooted it just before the hole problem occured.

    But i have to say: the last 2 day's the tempdb is "only" 4x8gyg. So if it stays at this size its fine by me. What the problem was..... i have no idea!

  • OK, One of the reboot does with tempdb is, even if you shrinked and it does not reflect the shrinkage, is to regain the space not used. That way we are keeping tempdb under control.

  • Hi All

    I have recently also been experiencing this problem where by the TEMPDb grew to 108GB over a weekend when no one was working and no addition jobs were scheduled other than normal maintenance and I have done no changes to the SQL Environment. I have added maintenance jobs for outside operating hours to keep it small until I come up with a proper solution.

    Any advice will be greatly appreciated

    Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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