The tempdb system database is used, as its name implies, to hold all temporary tables and temporary stored procedures.
tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there.
tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
In SQL Server 7, tempdb autogrows as needed. Each time the system is started, tempdb is reset to its default size. You can avoid the overhead of having tempdb autogrow by using ALTER DATABASE to increase the size of tempdb.
In SQL Server 6.5, tempdb's size is configured as it would be with any other database: if it's set up too small, complex queries (i.e. ones which require big work tables or reformatting), big cursors or large temporary tables can fill it.
Also in SQL Server 6.5, the default for tempdb is to be on the master device, along with the master database. Most admins will immediately move it to its own device: the process for doing this is documented at http://www.swynk.com/faq/sql/sqlfaq_transfer.asp#MoveTempdb
Category System Databases
( Topic last updated: 1999.12.07 05:01:45 AM )