What is Tempdb?

According to Microsoft SQL Server TechNet, the tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and stores temporary user objects, internal objects and row versions.

The tempdb is really just a temporary workspace for storing temporary tables, worktables that hold intermediate results during spooling, sorting or query processing, and materialized static cursors, which improve SQL Server performance.

Tempdb operations are minimally logged, which makes it possible for transactions to be rolled back. A new tempdb is created every time SQL Server is started so as to begin operations with a clean copy of the database. Furthermore, temporary tables and stored procedures are deleted automatically with a disconnect and there are no active connections when the system is shut down.

This means there is nothing in tempdb to be saved from one session to another. The minimal logging also means that the tempdb can only be configured in the simple recovery model.  By the same token, tempdb does not allow any kind of backup and restore operations.

Only the DBA has full access to the tempdb. Users can create objects in tempdb, but they can only access their own objects if they have been granted additional permissions.

Robert L. Davis, member of Idera Advisors and Community Educators (ACE) program and a senior database administrator, created a valuable webcast focusing on how to determine the optimal tempdb configuration for different types of SQL Server environments.  The webcast also covers identifying and preventing common tempdb performance issues.