October 5, 2005 at 1:58 am
Hi everyone.
Tempdb is growing from 250 Mb to over 30Gb within a 24 hr period.
We have job that shrinks the tempdb when there are no users connected which works fine.
Questions are , what is the best way to limit the growth of tempdb ? I have set the max growth to 250 Mb , following the shrink so hopefully this will ensure that is does not grow extensively again.
Anyone have any ideas on what I can look at for a cause ?
Is there anything that can be done to shrink and reclaim the un-used space the tempdb when users are logged in ?
Many thanks
October 6, 2005 at 2:50 am
There are a few things to consider...
tempdb could be growing to that size because it needs to. Possibly, tables are being created, lots of data are inserted, and the tables are not dropped after using them. The first thing that comes to mind though, is that dbcc checkdb() is causing tempdb to grow. Run "dbcc checkdb( database name ) with estimateonly" to see how much tempdb space is required for dbcc checkdb on the user databases (if you are dbcc'ing user databases on that server).
If it's growing to a large size each day, then, if you can afford the disk space during the whole day, just let it stay at the typical large size. That way, SQL Server doesn't have to grow the database each time it's needed, and therefore, slow down the actions that require the space.
Setting the maxsize may cause actions to fail if more space is required.
October 6, 2005 at 7:24 am
Temdb grows based on the sql that executes as T-SQL or in stored procs or UDFs.
When using temporary tables, instead of table datatype, tempdb resources are being used.
When using SELECT INTO, DISTINCT, ORDER BY, GROUP BY, tempdb is being used.
Run a SQL Server Profiler trace and change the sql that is overusing the tempdb resources.
Let me know if you have any questions.
Good Luck,
JP
October 10, 2005 at 11:42 am
see if this link can help you: http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply