rdotson102
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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!
To reduce the tempdb files follow AustinSeven comment's. Restarting isn't a solution!
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.
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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