May 3, 2004 at 8:44 am
I have a sys supporting a 24 x 7 application and have noticed recently that our tempdb is growing exponentially and wondered what the best method of managing this was. Because I recently took ove the responsibilities of managing this system, I am not sure if this is new or status quo.
Our largest application db is less than 13GB but i have seen tempdb as large as 18GB. I noticed especially during maintenance (reorgs and integrity checks) that this is a major problem. We recently ran out of space which caused a failover, and I am wondering if there are any known issues or suggestions. I had a job running weekly to 'shrinkdatabase' back down, but this is no longer sufficient, and I have read the articles outlining the problems which could occur if this is done while activity is ongoing.
What would happen if I set a "Maximum file size" and it reached this maximum?
Is there any other methinds of limiting the size of tempdb?
Thank you,
TLightbody
May 3, 2004 at 10:12 am
You have an evil query creating the growth in tempdb. At least this is my gut feeling on it.
Find the query or process that takes a long time to run and see if you can isolate it.
"Keep Your Stick On the Ice" ..Red Green
May 4, 2004 at 3:17 am
tlight - we had a similar problem and it turned out to be the way we created tempdb objects and didn't destroy them on persistent sessions. We re-worked the sp's to use the table variable and the problem has since resided. Another thing we noticed during this heavy activity period was a deadlocking problem - although not totally conclusive it pointed to :
<http://support.microsoft.com/default.aspx?scid=kb;en-us;328551>
Hope this helps.
Steve
May 4, 2004 at 10:00 am
Run a perfmon job to monitor the tempdb growth. That would atleast tell you what time the growth is accelerating. Then you might schedule a sp_who or something of sorts to track the process that is the culprit. BTW, if you do expect tempdb to be at certain size to handle your operations, I would not shrink it past that size, considering that it is a drag on the server to constantly grow and shrink the DB. In my case I have several 20 GB databases, but my tempdb gets to 25 GB for about 30 minutes a day when several huge aggregation queries run, so I always keep it at 25 Gb, even though for the rest of the day it is below 5 gb usage. If you set the max file size and if it reaches that size, whatever transactions were in progress in tempdb would fail and roll back.
May 5, 2004 at 5:29 am
I am guessing that the log is filling up because of a mainteneance job you are running. Namely under "Optimizations tab" of the Maintenance plan, the job reorganize data and index pages; this effectively runs the command DBCC DBREINDEX. I have seen this cause logs to grow to quadruple the size of the database.
Check you have specified the fill factor to the most appropriate setting for your system and look at your indexing policy in terms of frequency, which indexes etc. If you need any advice on this area go to SQL-Server-Performance.Com.
If you have time, take a copy of the db to create a test db and run this job to see if this is the cause. It certainly was for a couple of databases I have took over in the past.
May 5, 2004 at 11:59 am
Thanks to all for the information.
TLightbody
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply