SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issues with TempDB ever increasing


Issues with TempDB ever increasing

Author
Message
stormsentinelcammy
stormsentinelcammy
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 807
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
Attachments
tempdbsettings.jpg (20 views, 55.00 KB)
queryresults.jpg (21 views, 42.00 KB)
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1308 Visits: 8135
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? :-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

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.

- :-D
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207418 Visits: 41961
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stormsentinelcammy
stormsentinelcammy
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 807
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 Smile...good catch with that one, very few people catch that
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1308 Visits: 8135
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.

- :-D
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207418 Visits: 41961
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4352 Visits: 2741
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search