Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings.

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.

dm_db_task_space_usage

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? Any help on this problem would be greatly appreciated.

--Query that returned 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 
share|improve this question

migrated from stackoverflow.com Jan 13 at 20:28

This question came from our site for professional and enthusiast programmers.

2  
Since it are system spids, Are you using service broker or using any DDL Audting solution based on event notification like : sqlservercentral.com/articles/DDL+Auditing/88433? –  Edward Dortland Jan 13 at 21:13
    
Can you check select session_id, command from sys.dm_exec_requests to see if any session ids correspond with commands that begin with BRKR? If so can you have a look if there are many rows in: sys.transmission_queue or sys.conversation_endpoints. –  Edward Dortland Jan 14 at 9:30

2 Answers 2

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes.

Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file initialization enabled. You should really try to pre-size your file so that they're big enough to accommodate your busiest workload, and avoid growth events altogether. This isn't always practical, but in lieu of that, the growth size should be much larger. You really want this to be a rare and isolated event, not one that is occurring constantly. What is the point of keeping tempdb small, when it's going to use more space eventually? Are you going to lease the space out temporarily to the highest bidder, then evict them?

Also, why is there only one tempdb file? This is a common source of contention. Typical wisdom suggests to start with 4 files, even if they're all on the same disk. This can drastically reduce contention especially when multiple concurrent processes are trying to create objects or otherwise use space in tempdb. You may also want to look into enabling trace flag 1117 (which ensures that all of your data files grow at the same time) and trace flag 1118 (which changes extent allocation). Links about these below.

None of this will solve your core issue, of course - lack of (or concern over) disk space. If you don't have enough disk space to support the current usage of your system (whether it's the system or your users), get more disk space, or move the system. You might be able to find some of the culprits (see this answer for some common ones), but you won't be able to squash them all.

You might also find these things useful:

And also, now that we know you are using Service Broker, you may want to read these two pages which might help explain why your conversations aren't clearing out:

share|improve this answer
    
very good point on the times the data file will have to grow, i will change that immediately. you're also right about the growth being expensize, but ultimately this just solves more settings issues than the real issue i have at hand. Could my settings be the cause of the ever growing issue? my biggest concern is the fact that the file is ever growing with no active queries, i know something has to be using it, but i'm not entirely clear on how to go about catching the culprit effectively –  nightmareivy Jan 13 at 20:49
    
thanks again for your help aaron, i'll look into it and give you my results going forward, your help is much appreciated up to this point –  nightmareivy Jan 13 at 20:55
    
i actually read up on that link you posted on your second comment about 4 hours ago and there are no running sql transactions when i run that script, but the script that i pasted i found all of those internal spid's running, i'm just very puzzled as to what causes this, but i am changing the settings right now for tempdb –  nightmareivy Jan 13 at 21:00
    
@nightmareivy well, with just a list of SPIDs, we can't really tell you either. :-) Also that comment was somehow deleted during the migration, probably because it included a link to this site (typically those are discarded as redundant once the post has moved). Though that doesn't explain why I had to manually delete the other comment, which also included such a link. –  Aaron Bertrand Jan 13 at 21:04
    
that's not good...maybe you're going to have the same weird issues that i'm having :), FYI i've read most of the links that you posted already, paul white/randal are very good at this kind of stuff and even better people in person...in any case, would you happen to know a query that could be run on tempdb to find out the source of the spid? i know its asking for a lot, but everything that i've read and looked into today points to something outside of the instance, unless i'm totally missing something really obvious –  nightmareivy Jan 13 at 21:08

Based on the settings you have listed your tempdb is set insanely small in my opinion. For a small instance try starting out with a 1000MB data file and a 500MB log file. Then change your autogrowth to 100MB for data and 50MB for log. Then monitor. If you are still getting frequent growths then try upping your initial sizes by X10 and your growths by x2 and monitor again. Tempdb doesn't usually just grow randomly. Something is using it.

Now if this is your personal PC then maybe try 100MB and 50MB respectively but even then I would expect some level of growth when you are actually doing things.

All of this of course is based on how much space you have for tempdb. Several of my production servers have tempdbs of 100GB and I've seen MUCH larger.

share|improve this answer
    
good points kenneth, but those are more issues to do with the settings of tempdb, i'm not entirely sure that it will help me catch what exact process is making tempdb run up in size, also all of my spid's seem to be internal process spid's could there be a reason why those same spid's in the picture continue to increase in size or at least how to trace the possible culprit of this? –  nightmareivy Jan 13 at 20:51
    
I would increase your size to a reasonable amount and then see if it continues to grow. SQL does use tempdb for internal processes as well as external so I would start with the assumption that it's just normal usage unless proven otherwise. –  Kenneth Fisher Jan 13 at 21:28
    
you're right kenneth, but after more investigating and with some suggestions from aaron and edward, the issue seems to be with service broker, many of the messages are still in the "conversing" state and thus have not ended, which in turn causes the messages to stay in tempdb –  nightmareivy Jan 14 at 14:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.