Post reply

Script to find which active transaction is using more space in tempdb

  • December 22, 2016 at 7:29 am

    #319868

    Hello,

    Can someone help me with script of finding transactions which are heavily using temdb space in their queries.

    Thanks in advance

  • December 22, 2016 at 7:36 am

    #1919333

    Have you looked at sp_whoisactive?

    John

  • December 22, 2016 at 7:44 am

    #1919337

    This is something like I need to send an email alert every hour so DBA team can look into the usage of Tempdb on hourly basis. The sp gives info only for that session and I can't use for my alert i guess.

  • December 22, 2016 at 7:48 am

    #1919341

    It gives info for all active sessions. If you have the patience, you can look at the stored procedure definition and try to find the part that retrieves tempdb usage, and see if you can use that as a basis for something to fit your requirement.

    John

  • December 22, 2016 at 8:01 am

    #1919345

    Due to my access restrictions, I can't use that and I don't need that much detailed level of informtion. Is there any other script that I can use to send email alerts on which query is consuming tempdb much..

  • December 22, 2016 at 8:26 am

    #1919351

    If you have such restricted access it sounds as though you're not the DBA, so you can probably hand that task over to whoever is.

  • December 22, 2016 at 8:43 am

    #1919360

    Hope below blog helps

    https://blogs.msdn.microsoft.com/deepakbi/2010/04/13/monitoring-tempdb-transactions-and-space-usage/

  • December 27, 2016 at 10:00 pm

    #1919819

    also take a look at this link: http://www.sqlservercentral.com/scripts/tempdb/72007/[/url]

  • December 28, 2016 at 9:17 am

    #1919889

    Beatrix Kiddo (12/22/2016)

    If you have such restricted access it sounds as though you're not the DBA, so you can probably hand that task over to whoever is.

    Heh... +1 million to that. I was going to suggest that the DBAs learn how to be DBAs.

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

Viewing 9 posts - 1 through 8 (of 8 total)

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