• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7017
  • Last Modified:

tempdb suddenly growing too large

For some reason, all of the sudden my tempdb has started growing to the limits of my harddrive space on the server and I can't figure out why.  Yesterday, I was testing an sp that ended up running for about 15 minutes and I noticed the tempdb hit 21GB, so I restarted the server to reset it.  Since then I haven't run anything on it other than the normal operations and when I came in this morning, it was at 21GB again.  The only db that's running is about 75MB and may have had two people on it.  I didn't have any scheduled events or anything that I can think of.  Any idea what's causing this?  I can expand the storage area for it with no problem, but it doesn't seem right for it to be this large.  It's never been in the GB range before.
0
rdotson102
Asked:
rdotson102
2 Solutions
 
imperial_p79Commented:
it there is plenty of temp tables usage in any of your stored procedures in any of the databases on the server, this can cause this problem.
0
 
ptjcbCommented:
FROM
http://www.red-gate.com/other/tempdb_growth.htm
***

Readers often come to me with stories of runaway tempdb growth. One DBA recently installed a newer version of an application and saw his tempdb, set to start at 80MB, suddenly grow in huge leaps to 8GB or more. How do you track tempdb growth and correlate that growth to specific commands?

Here are three effective ways to track tempdb's growth. One, you can use the Performance Monitor counter Data File(s) Size (KB) associated with the tempdb instance for the database's performance object. This counter is a good way to find out when the tempdb file is growing. You can also access this data directly from SQL Server by querying the master..sysperfinfo table, which materializes all Performance Monitor counters that SQL Server exports. Two, you can periodically sample tempdb's sp_spaceused output. And three, you can use the fn_virtualfilestats() function to track the I/O that SQL Server writes to the database files that tempdb uses. If tempdb is growing, the likely cause is the physical I/O associated with certain queries. And if that's the case, monitoring fn_virtualfilestats data can help you narrow down the queries responsible for the tempdb growth. Any of these techniques let you track tempdb growth over a specific time period.

To correlate tempdb growth with the queries that caused the growth, you can run a SQL Server Profiler trace while tracking tempdb growth. There's no direct way to find out which query caused tempdb to grow by how much, but you can make some assumptions that will help you track down the potentially offending queries. The primary assumption is that any query that can make tempdb grow by large amounts, such as the DBA above saw, will take a long time to run compared to typical queries. These queries would also likely show a relatively high value in the WRITES data column of a Profiler trace. Seeing SPOOL operators in the query plan is another sign that SQL Server is performing large numbers of writes to tempdb. The SPOOL operator means that SQL Server is "spooling" an interim result set to a secondary location (tempdb) so that it can do something with that data later in the plan.

If you've captured data that helps you understand when the tempdb files were growing or experiencing large amounts of I/O and you've captured a list of queries run during the same period, you should be able to analyze the query list. Look for queries that have a start or end time that maps to a period of high growth, then further limit the list to queries that took a long time to run (probably at least a few hundred milliseconds) and performed a reasonably high amount of I/O operations (relative to other queries running at the time). This analysis will give you a short list of queries that could be causing the dramatic tempdb growth.

Here's another idea to consider: In my experience, a full or partial Cartesian product can cause massive tempdb growth—and don't forget to look for those SPOOL operators! Profiler has an event called Missing Join Predicate in the errors and warnings event class that will capture situations in which SQL Server thinks that a JOIN predicate is missing. I've never tested the logic to see how SQL Server determines a predicate is missing, so I can't say that it always finds a missing JOIN predicate. But I include this event in my standard trace analysis and have successfully identified many Cartesian products (that were previously unknown to various customer development teams that I was consulting for) by looking for instances of this event.
0
 
AustinSevenCommented:
use tempdb
exec sp_spaceused

or... click on taskpad in EM for tempdb.

You will probably see that the file size of tempdb tran log is big but the usage is only a small fraction of the file size.  What you did with your stored procedure grew the tempdb tran log file but the usage has since reduced.   Therefore, to reduce the size of your tempdb tran log, you need to shrink it.

DBCC SHRINKFILE (templog, 1000)  

The above indicates a target size of 1000mb - set it to whatever you think is reasonable for your server.  Sopmetimes the above won't work and you have to use ...

BACKUP LOG tempdb WITH  NO_LOG

Give it a go with shrinkfile first.

AustinSeven

0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
tempdb doesn't grow like that if you don't have a process that holds big amount of data running. Try to find it out.
To reduce the tempdb files follow AustinSeven comment's. Restarting isn't a solution!
0
 
mastooCommented:
Here's a nice link:

http://www.aspfaq.com/show.asp?id=2446

My favorite is the last one where somebody has the odbc option turned on that fills up the tempdb as long as the connection is around.
0
 
rdotson102Author Commented:
My templog file is 760K.  The db file is the one that's huge.  When I run "use tempdb exec sp_spaceused" it gives me a db size of 20,644.69MB with 20,643.20MB unallocated space.  When I restarted the server the other day, it reset the log and retured it to the normal 80MB size.  There are no processes running on it and it hasn't changed size in the last day.  My program doesn't use any temp tables and my main db has as much spce as it will ever need.  I can't figure out why it was using the tempdb to this extent.  Is it possible that my sp that had some bugs in it ran the tempdb size up and then sql set that size as the correct size for it?  I don't think that's it, since I checked it's size after the restart like I stated above.  Also, I have since freed up space on the server, so it's no longer hitting disk space limits but it hasn't increased in size anymore.  What do you think?
0
 
AustinSevenCommented:
tempdb is recreated every time sql server is restarted using Model as the... model.  So that explains why the size issue got sorted out.   You could have used dbcc shrinkfile to shrink the tempdb data file and avoided the restart but that's minor point.    You can only be certain as to what caused the growth by recreating the problem.  If you can't recreate the problem, forget it.   If you can recreate the problem, use Profiler to find the reason.

AustinSeven
0
 
rdotson102Author Commented:
I'm trying to use dbcc shrinkfile on it now, but it's telling me "could not locate file tempdb in sysfiles."  You'll have to excuse me, I'm not a dba.  I know sql pretty well for development but not maintenance.  What am I doing wrong?
0
 
AustinSevenCommented:

use tempdb
sp_helpfile

then you'll see the logical names of the data and tran log files.   It'll be 'tempdev' probably.  Then use that name in the dbcc shrinkfile.   Don't make it too small 'cause you want to leave room for normal increases in usage.

AustinSeven
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now