kevaburg (7/19/2013)
Steve-3_5_7_9 (7/10/2013)
kevaburg (7/9/2013)
There is nothing to worry about. TempDB is recreated each time the instance/server is restarted and it will consume however much space it requires to perform its function. The fact it is so small by a restart is nothing to be concerned about as (by the very nature of the file itself) the data contained within is temporary.It absolutely is something to be concerned about. You want to have TempDB properly sized, so in this case the OP would want to size it at 2GB or higher. If you allow TempDB to "autogrow" you will most likely have performance issues as the TempDB file(s) grow.
Steve
This is something that could become very contentious. Why would growth on the TEMPDB be a problem if the database itself is properly sized (the DB determines that itself) and growth rate is properly set?
This, from my point of view, only becomes a problem if the database server is restarted on a regular basis. The people that insist on restarting a Windows Server / Database Server make this a problem all by themselves.
I don't think the problem is in answering the question "why doesn't it maintain the settings I give it" rather "why was the instance restarted in the first place?"
Because TEMPDB is recreated each time the instance is started, it must also be populated each time the instance starts with the data the needs to be loaded. Resizing (if done properly) is not a huge issue in SQL Server although I am not taking away the fact that it requires resources to perform. The most intensive operation here is repopulating the database so it can operate in the way it should. This is the equivalent of restarting an Oracle database and wondering why each initial query execution is slower than normal simply because the shared pool has been flushed.
Anyway, that is why I believe it isn't a problem.....
(Sorry the answer took so long.....hoilday has a somewhat higher priority!)
I guess I don't understand what you're talking about when you speak of "repopulating" TempDB. TempDB requires no repopulation on restart. Some things in the system do use it once TempDB has been restarted but it doesn't require "repopulation" in any classic sense. Allocating the space for it takes no time if you have instant allocation enabled. And, TempDB has nothing to do with "shared pool" stuff. That would memory/cache where data and execution plans are loaded and, hopefully, reused. TempDB has little to do with any of that.
Perhaps when you're speaking of "repopulating 'the DB'", you're not talking about TempDB but are talking about some other data DB?
--Jeff Moden
Change is inevitable... Change for the better is not.