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

Question

Post reply

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

wweraw25

SSC Eights!

Points: 953
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

December 22, 2016 at 7:36 am

#1919333

Have you looked at sp_whoisactive?

John

wweraw25 SSC Eights! Points: 953 More actions · Answer 2

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

wweraw25 SSC Eights! Points: 953 More actions · Answer 4

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

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.

Sreejith! SSCrazy Points: 2401 More actions · Answer 6

Hope below blog helps

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

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 7

also take a look at this link: http://www.sqlservercentral.com/scripts/tempdb/72007/%5B/url%5D

Jeff Moden SSC Guru Points: 1004601 More actions · Answer 8

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)

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

Cookies on SQLServerCentral