Issues with TempDB ever increasing

Question

Issues with TempDB ever increasing

stormsentinelcammy

SSCrazy

Points: 2493
More actions
January 13, 2014 at 11:28 am

#396101

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

Viewing 7 posts - 1 through 6 (of 6 total)

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

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 1

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[/url]

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? :-D)

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[/url]

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.

- 😀

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 2

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.

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)

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 3

The version i'm running is the one below:

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

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 4

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.

- 😀

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

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.

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)

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

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/[/url].

Anyone with more experience, please chime in - I'm probably doing a poor job explaining how to pick patches.