how to control its size or some alternate so that tempdb size does not grow
that much
Francis - 30 Jan 2006 19:34 GMT
"By default, the tempdb database automatically grows as space is needed
because the MAXSIZE of the files is set to UNLIMITED. Therefore, tempdb can
continue growing until space on the disk that contains tempdb is exhausted.
To prevent tempdb from growing without limits, set a MAXSIZE for tempdb by
using the ALTER DATABASE statement or SQL Server Enterprise Manager."
From BOL
Ask yourself if you want to restrict the growth of tempdb, you may impact
performance. Make sure tempdb is sized properly to begin with. For example,
if tempdb quickly grows to 500 MB maybe the default size should be 500 MB so
that when you restart the server, it is not necessary for tempdb to begin the
process of auto growing.
> how to control its size or some alternate so that tempdb size does not grow
> that much
David Gugick - 31 Jan 2006 04:52 GMT
> how to control its size or some alternate so that tempdb size does
> not grow that much
It only grows in response to the temp db space required on the server.
Large sort operations to support merge joins or as a result of ORDER BY
clauses, joins without index support, GROUP BY clauses, etc. can all
cause a lot of work in tempdb. If you tune your SQL, you may find that
tempdb usage goes down quite a bit.

Signature
David Gugick - SQL Server MVP
Quest Software