x

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
more ▼

asked Nov 09, 2015 at 10:59 AM in Default

avatar image

Droggot
10 3

I tried reformatting this code 3 times. Each time, it shows formatted in preview but then not when I save and refresh.

Nov 09, 2015 at 12:45 PM Tom Staab ♦

What do you mean by "then this script doesn't work"?

Nov 09, 2015 at 12:46 PM Tom Staab ♦

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

Nov 09, 2015 at 01:07 PM Droggot

@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

Nov 09, 2015 at 01:10 PM ThomasRushton ♦♦

@ThomasRushton - Thanks for fixing it and for explaining.

Nov 09, 2015 at 03:15 PM Tom Staab ♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Nov 09, 2015 at 03:47 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

Thank you Tom that fixed my problem!

Nov 10, 2015 at 07:22 AM Droggot
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x83
x78

asked: Nov 09, 2015 at 10:59 AM

Seen: 75 times

Last Updated: Nov 10, 2015 at 07:22 AM

Copyright 2017 Redgate Software. Privacy Policy