Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    17

    Unanswered: isolating SQL Query!

    hello
    Since the past couple of months we have been seeing the tempdb grow from 5GB to 22GB about once every week. The production database is about 35GB. Since there is quite a bit of activity on the server i am finding it difficult to try to isolate what caused this sudden increase in the tempdev size. The actual data in tempdev less then 2 MB but it holds on to the 22GB till i go in and shrink it.
    This has led to us having serious space issues on the server.
    Any advise on how i would be able to trap the query that might cause this behavior would be great.

    thanks

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Setup a trace using SQL Profiler

  3. #3
    Join Date
    Oct 2002
    Posts
    17

    profiler..

    I am not sure how i can run the profiler to isolate the query that causes the tempdb size to shoot up.
    Can you recommend the parameters that i should set up on the trace that would help me do this.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Start and END SQL?

    Then load it to a table to see the longest running queries

    I thought I asked this....what's the login that runs your application...

    What do you development in?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    As an immediate measure to prevent space issues on your server, I would set a maximum limit on the growth for the tempdb data file. I have heard elsewhere (but seen no documentation) that 1.5X RAM is a good starting point (and yes, I realize that this is the same as for the paging file, but I do recall that this is a separate setting).

    Regards,

    hmscott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •