Post reply

TempDB

  • February 19, 2004 at 5:37 am

    #82002

    Is there any way to find out who and what is increasing the size of TempDB?

  • February 19, 2004 at 6:49 am

    #494686

    Pretty much the who is SQL Server, any action that generates temp tables such as temp table creation, order by, group by, some bulk inserts and other items can impact this database and cause the size increase to trigger. If using SQL 2K you can shrink it down without issue. If using SQL 7 I have had issues with SQL 7's EM not working it down but download SQL 2K trial or full product and use that EM will work (suggest use a seperate box to install the client tools). But there are lot's of factors that use tempdb.

  • February 19, 2004 at 7:23 am

    #494697

    Thanks for the infos (we're running SQL 2K SP3a by the way) the problem is not how to shrink the DB though it's more that I'm looking to find out whether it's a stored procedure or just someone doing a huge sorted select. I was wondering if there was some tool to look at the log file perhaps to see what's writing to TempDB.

  • February 19, 2004 at 7:44 am

    #494703

    The best tool at your disposal is Profiler, set it to monitor the server for the following events should give you an idea.

    Database - Data File Auto Grow

    Database - Log File Auto Grow

    Stored Procedures - RPC:Completed

    TSQL - SQL:BatchCompleted

    Then start the trace, around the time you see any growth for TempDB look at the statements that ran. This should help narrow it down. Like I said, order by, compute by, group by can all have the effect of creating a temp table to do the sorting of the data so those will be your primary candidates.

    As for a tool to look at the log, the log is automatically truncate for the TempDB and if I rememeber correctly you cannot change that.

  • February 19, 2004 at 11:34 pm

    #494858

    Thanks for the tip with profiler I'll test it out next week when the database is active again!

  • February 19, 2004 at 11:49 pm

    #494861

    Watch the sysobjects table in tempdb too 🙂


    "Keep Your Stick On the Ice" ..Red Green

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

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