March 31, 2010 at 6:53 am
My tempdb, which hardly ever gets to over 10GB (almost all of it data), got to about 12 GB, within 1 GB of the space limit on the drive it's on.
I wound up doing a truncate of the log (even though it was very small, about a few hundred MB), then did a shrinkfile on the data file, then a shrink database. Any attempts to do the shrinks w/o first running the truncate of the log did not shrink the file or the database.There were no active processes running.
This is a production sql server and I can't recycle it whenever I want to. However, I'm not thrilled to read the MS article about possible corruption [highlight=#ffff11]http://support.microsoft.com/kb/307487[/highlight]
.
Isn't there some other way for tempdb's data part to shrink? Is there nothing in the sql server 2005 db engine that trims the data part of tempdb as a periodic maintenance function that is done in the background? I know with the simple recovery model, the log is periodically cleaned up. What about the data part? If a user runs a very large query or something bad like a Cartesian product, the data part will expand and approach my space limits.
Isn't there anything else I can do w/o the risk of corruption and w/o recycling?
March 31, 2010 at 7:23 am
I don't think so- my understanding is that the methods described in http://support.microsoft.com/kb/307487 are the only ways to shrink tempdb.
The question is what causes the tempdb to grow so large and is that normal? If you have a process that requires that much temp space which runs regularly, there is no point shrinking it down if it will just grow to that size again...
Brian Moran discusses methods to track tempdb growth on his blog here: http://www.simple-talk.com/sql/database-administration/tracking-tempdb-growth/
Do you perform regular re-indexing operations against large tables?
March 31, 2010 at 7:39 am
I am looking into what may have caused it. Some information is logged by a stored proc that looks for space increases and sends emails to alert me.
I just took over the sql server and I'm learning how things run on it.
I know it's been up 42 days. We recycle every 30 to 60 days, so there is plenty of time for some unusually large query to run. It's been mostly at 5 GB, then just went up to > 10 yesterday within 5 minutes.
We reindex with a plan every Sunday, so that is not the cause.
My main question right now is if there are alternate methods to shrink temp data file w/o possible corruption and w/o a recycle.
March 31, 2010 at 8:03 am
Not that I know of. I can tell you that I have used dbcc shrinkfile as described in method 3 of the kb, and never had a corruption problem... but each time I have done so I knew I was taking a risk.
March 31, 2010 at 8:07 am
Right, and I think I'll have to take that risk.
The worst that happens is I have to then ask for a reboot right away vs. waiting for a more convenient time like in the evening.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply