Post reply

TempDB is 13gigs and growing

  • January 9, 2008 at 3:15 pm

    #202211

    Hello everyone. I am back.

    I inherited a server that is running Microsoft SQL 2005. I was doing some routine server maintenance when I received a warning that I was running out of space on this server.

    Poking around, I find that the tempdb is almost 14gigs.

    I am not a DBA, mostly working with AD and such, but I have done a little bit of work in the past.

    Anyway, is it possible to some how clear up that space? Seems like an awful lot of space being used for the tempdb. From what I recall, this was just a temporary place to hold the transactions, but is flushed periodically?

    I am going to setup two additional drives in the server and create another array so I can put the data and log files on it (should give me 100+gigs of space...not a heavily transactional server). Once that is in place, i was going to move all the database files over to that location.

    Anyone have some suggestions on what to do in the meantime?

    Thanks,

    Jason

  • January 10, 2008 at 3:26 am

    #766544

    There can be multiple reasons around this:

    1. Is there any replication set up on the server, if yes, is it running fine?

    2. Do we have any job or stored proceudre which is initiating lot of transactions internally?

    you can try to execute the following command:

    backup log tempdb with truncate_only or alternately you can try to shrink the database by right clicking on the database and choosing the option to shrink.

    HTH

  • January 10, 2008 at 7:00 am

    #766619

    We ran into a problem recenlty where an app was leaving open connections and open transactions that were filling tempdb. You might look for old open connections, especially since it's constantly growiing.

    dbcc opentran('tempdb')

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 10, 2008 at 9:04 am

    #766703

    Do you have frequent database/log backups in place? Are they running at all?

    Paul

  • January 10, 2008 at 9:17 am

    #766710

    You could just need that much tempdb space. It all depends on your queries. It's used for sorting, etc.

    You can shrink it down, but it could grow right back up.

  • January 16, 2008 at 3:00 pm

    #768982

    Sorry for the late response. Have been trying to do some research on this.

    As of now, it seems to be holding at 17gigs. I have not seen any further growth, so I am not sure what to expect.

    On a side note, looks like my company will be sending me t some SQL training (thankfully) since we don't have a resident SQL guy. That is fine with me. I have always found databases fascinating.

    In the meantime, im going to try to get up to speed a bit on SQL in general.

    Appreciate the help. If anyone has any suggestions, fire away.

    Oh, i did try to see if there were any open transactions on the tempdb, but it came back negative.

    Perhaps it does need to be that big?

    Is there a way for me to tell how the specific application writes to the database and tempdb? Maybe it needs a large tempdb? A way to find out?

    Thanks.

  • January 17, 2008 at 2:52 pm

    #769466

    I believe that in Sql 2000 the tempdb and log file get rebuilt when you reboot the server. Not sure if this applies to 2005. Of course, this can be hard to have a time when you can do this with a production server.

    Steve

  • January 18, 2008 at 4:58 pm

    #769906

    The tempdb gets rebuilt each time the SQL Server Service is restarted in both 2000 and 2005. I would not recommend shinking the tempdb as it may have to auto-grow back to the original size and this causes a performance hit to SQL. This site also has tons of posts about tempdb.

  • January 18, 2008 at 7:41 pm

    #769919

    17GB seems a bit large even if it's a Terabyte instance... if folks are using triangular joins, cursors for large record sets, temp tables for large record sets, views of views, are are trying to join too many tables at 1 time, that might explain the size of TempDB... but then that code needs to be fixed.

    We've got a Terabyyte instance and temp db is very happy at about 9 gig.

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

  • January 19, 2008 at 10:08 am

    #769951

    Once you find the offending code however - restart the server and see what size the tempDB naturally grows to. Once you have a stable number - make that the starting size of your tempDB. No sense in forcing it to grow in a bunch of small increments (it will actually hurt your performance to do that).

    You may also just have some HUGE transactions. Rebuilding an entire 100GB database for reporting might not be the greatest thing to have in a single transaction. Perhaps finding them and cutting them into smaller pieces would help.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 22, 2008 at 7:24 am

    #770452

    Am I correct in thinking that the size of the model database data and log files is the original size for tempdb when it is recreated? To change the original tempdb size for data and log you would need to change the size of model before taking Sql Server down and back up?

    Steve

  • January 22, 2008 at 9:19 am

    #770503

    Yes and no

    Model is used for all databases, including tempdb.

    you can expand tempdb and it will start there on the next instance restart.

  • January 22, 2008 at 9:24 am

    #770510

    Model and tempDB don't have the same size. The starting size attribute doesn't carry over.

    As of right now - my Model DB has a size of 1.19MB, whereas my TempDB starts out at 4GB.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 22, 2008 at 12:30 pm

    #770614

    Following up here.

    Well, this is turning to be into quite a task (but I am up for it) trying to figure out. Learning a ton, so that is good.

    Let me fill everyone in.

    Last I checked, the tempdb database file, was at 17gigs. Seemed steady for a good week. No changes, just that the tempdb.mdf was at 17.2gigs.

    I ran into some issues with the server that required me to add some extra disk space and move some of the transaction logfiles over to the new array. I was getting horrible I/O on the disk side of things which was causing the application to crash, freeze and hang. Moving the tempdb .mdf and .ldf to the new array seems to have made a huge difference.

    Ok. So this morning, before I did the move, my tempdb.mdf file was 93gigs!! I was like "wooow"!

    I'm really new to all of this so I am not quite sure where to look and begin. There are only two databases on this particular SQL Server. They are relatively small, but im thinking one of them has a lot of transactions.

    A few things.

    1.) how can i find out why my tempdb.mdf blew up to 93gigs? What do I do? I did restart the server and the tempdb file went down to 8mb in size and has been steady there for awhile.

    2.) Like I said, very new here and learning. how can I see which of the two databases could be the culprit for the tempdb database growing so large?

    I appreciate the help.

    Jason

  • January 22, 2008 at 12:47 pm

    #770619

    You've got someone running transactions, sorts, or creating temporary tables with lots of data. We had users starting an ORDER BY query (no parameters, bad joins, bad search criteria) that, when it ran long (as it almost always did), they'd cut the connection on the app. For some reason their code maintained the connection through the application server and the transaction continued to sort the records, chewing up tempdb space for days until we got a failure. Simply cleaning up the query and creating a better index fixed the problem. But first, you need to identify it.

    If you don't have a third party monitoring program, you can roll your own to check open transactions in tempdb every 10 minutes or so. If anything has been open longer than since the last check, write it out to a database. In the morning, you should be able to at least identify the poor performer. From there, figure out what they're doing wrong.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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