question

Droggot avatar image
Droggot asked

How can i se what table eats up tempdb

Hello. I have a strange problem with an application thats eats upp tempdb. I would like to know the tables name and how large this table is. I found a script on the web that solves this but when this application starts eating up tempdb i can only run select statments that have with (nolock)on them and then this script doesn't work -- Temporary Tables and Their Size use tempdb SELECT TBL.name ,STAT.used_page_count ,STAT.row_count ,STAT.used_page_count * 8 ,STAT.reserved_page_count * 8 FROM tempdb.sys.partitions AS PART INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT ON PART.partition_id = STAT.partition_id AND PART.partition_number = STAT.partition_number INNER JOIN tempdb.sys.tables AS TBL ON STAT.object_id = TBL.object_id ORDER BY STAT.used_page_count DESC
sql server 2008 r2tempdb
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I tried reformatting this code 3 times. Each time, it shows formatted in preview but then not when I save and refresh.
1 Like 1 ·
@Tom Staab - re: script not showing formatted - I suspect this is either due to a <pre> (or was it a </pre>) tag in the script block
1 Like 1 ·
What do you mean by "then this script doesn't work"?
0 Likes 0 ·
It starts running but I never get any result untill the process that eats up tempdb is killed or I stop my Query. I get the same problem with this select statment it never give me any result. select * from sys.allocation_units order by total_pages desc But if I use this one it works. select * from sys.allocation_units with (nolock) order by total_pages desc
0 Likes 0 ·
@ThomasRushton - Thanks for fixing it and for explaining.
0 Likes 0 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
Try adding this before your query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. That essentially applies the NOLOCK hint everywhere. I do not recommend this in most cases, but it's fine for a query like this. Honestly, though, since it sounds like it's okay to get scripts from the internet, I recommend you check out Adam Machanic's sp_WhoIsActive. It will tell you everything you've asked and more. [ http://sqlblog.com/files/folders/beta/entry42453.aspx][1] [1]: http://sqlblog.com/files/folders/beta/entry42453.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Tom that fixed my problem!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.