GilaMonster (2/28/2013)

---

ScottPletcher (2/28/2013)

---

GilaMonster (2/28/2013)

---

ScottPletcher (2/28/2013)

---

GilaMonster (2/28/2013)

---

ScottPletcher (2/28/2013)

---

If the temp files are growing, I don't want to allocate them smaller and force them to grow every time SQL starts: that's a waste of resources vs just pre-allocating them large enough.

Indeed it is, but so's shrinking TempDB on startup. I'll typically have monitoring or automatic reports on autogrow events (for any DB) and TempDB utilisation.

I capture autogrow events as well. But that doesn't prevent them from reoccuring every start up for tempdb. So you force a manual process to increase tempdb rather than doing it automatically?

Sure. If I've planned correctly then the only things that will cause a TempDB growth is an unusual event that won't reoccur (in which case I won't grow TempDB) or TempDB usage growing beyond what is currently allocated and me having been careless and missing the signs of that.

I don't miss the signs of it.

I, too, log when autogrow occurs anywhere. And notify immediately when it occurs on tempdb.

You just have to have someone manually intervene to correct it. I auto-adjust it whenever possible, so that the error can be corrected w/o requiring manual intervention.

Yes, a person reviews and re-adjusts later if necessary, but performance doesn't stay degraded even if someone can't manually intervene.

One big difference. If I'm paying attention the autogrow never happens. I'll have seen the increased usage of TempDB and manually grown the files (at a quiet time) before the autogrow would be required.

TempDB autogrowing is a sign that I've stuffed up, not done my job properly.