Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issues with TempDB ever increasing Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 11:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:12 AM
Points: 120, Visits: 691
Hi,

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then slowly without stopping. I've run many queries to figure out what is running, below is the result of the query below which actually gave me the results I could use. As can be seen they are all internal spid's is there any way to find out why tempdb continues to grow out of control and how to mitigate it?

--Query that gave the result set
SELECT session_id
,SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count
,SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id
HAVING SUM(internal_objects_alloc_page_count) > 0


  Post Attachments 
tempdbsettings.jpg (7 views, 55.55 KB)
queryresults.jpg (7 views, 42.59 KB)
Post #1530397
Posted Monday, January 13, 2014 2:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 590, Visits: 6,853
Hmm. A bit more information is necessary here. What's the size of tempdb when it's grown out quite a bit? From your settings, it looks like tempdb is configured to grow in very small increments; typically (though this is a very "it depends!" sort of thing), tempdb will be decently large, depending on your environment.

This is largely because tempdb is the crux of many operations that get performed in queries, such as sorts, temp table allocation, and tons of other things. Those operations, if called enough, will cause growth, sometimes in very large amounts. I believe even the internal workings of SQL Server will be using tempdb quite a bit.

For further investigation, I'd recommend reading SQLServerCentral's Stairway to Server-Side Tracing:

Stairway

I'd suggest setting up a server-side trace and running it to capture what's running on the server, particularly when you say there's no queries running; it may be internal operations causing tempdb strain, or there could be other unknowns that could be influencing tempdb usage.

Let us know what you find, and someone will probably be able to offer assistance!

(On a side note, interesting user name! Play Marvel 2 often? )

EDIT: Well, nevermind! A trace might be a bit overboard (though still intriguing); did some digging, since the topic sparked my interest. Here's a great article by Jeremiah Peschka about figuring things out about tempdb:

Article

In particular, the last query in the article should match up to the results from the query you've already done; from there, you can take the contents of the sql_handle column and plug them into sys.dm_exec_sql_text() between the parenthesis to get the exact query that caused the usage.




-
Post #1530459
Posted Monday, January 13, 2014 2:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
stormsentinelcammy (1/13/2014)
Hi,

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then slowly without stopping. I've run many queries to figure out what is running, below is the result of the query below which actually gave me the results I could use. As can be seen they are all internal spid's is there any way to find out why tempdb continues to grow out of control and how to mitigate it?

--Query that gave the result set
SELECT session_id
,SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count
,SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id
HAVING SUM(internal_objects_alloc_page_count) > 0


I can't put my finger on it but I remember something about a fault with SQL Server 2005 where if you had certain intial settings, TempDB would run away like this. I know that's not much help but it may help you with a Google search or may jog someone's memory that actually has a URL for this fault.

What is the Service Pack level of your 2005 installation?


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530468
Posted Monday, January 13, 2014 2:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:12 AM
Points: 120, Visits: 691
The version i'm running is the one below:
Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I believe it could be something to do with the service broker since there are no procedures and/or queries that run or are the source of the problem, if its the service broker then its foreign territory to me and i'll have to dig into it more...

I used to play mvc2 :)...good catch with that one, very few people catch that
Post #1530482
Posted Monday, January 13, 2014 3:00 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 590, Visits: 6,853
Aha! With what Jeff mentioned, it seems that you may indeed be a victim of the aforementioned bug:

Microsoft Fix Notes

It seems that this problem was fixed in a Cumulative Update to Service Pack 2; since you're still on Service Pack 1, you're open to being subject to being affected by the bug.

If at all possible, I'd say an upgrade to the most recent service pack would be preferable; that should (hopefully) clear the issue up.




-
Post #1530483
Posted Monday, January 13, 2014 3:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
hisakimatama (1/13/2014)
Aha! With what Jeff mentioned, it seems that you may indeed be a victim of the aforementioned bug:

Microsoft Fix Notes

It seems that this problem was fixed in a Cumulative Update to Service Pack 2; since you're still on Service Pack 1, you're open to being subject to being affected by the bug.

If at all possible, I'd say an upgrade to the most recent service pack would be preferable; that should (hopefully) clear the issue up.


Heh... memory jogged. Thanks for jumping in here.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530485
Posted Tuesday, January 14, 2014 8:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
hisakimatama (1/13/2014)
Aha! With what Jeff mentioned, it seems that you may indeed be a victim of the aforementioned bug:

Microsoft Fix Notes

It seems that this problem was fixed in a Cumulative Update to Service Pack 2; since you're still on Service Pack 1, you're open to being subject to being affected by the bug.

If at all possible, I'd say an upgrade to the most recent service pack would be preferable; that should (hopefully) clear the issue up.


I'd like to expand on that a little - I'd recommend updating to anything from build 5200 to 5323 (for simplicity's sake, apply SQL2005 SP4 CU3), and then (re)apply the MS12-070 security patch to bring you up to build 5324 - you'll need the QFE ('with CU') version instead of the GDR ('with SP only') version you used before. http://sqlserverbuilds.blogspot.com/.

Anyone with more experience, please chime in - I'm probably doing a poor job explaining how to pick patches.
Post #1530728
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse