Ravi Khanal

When SQL Server is installed the setup program creates tempdb database. The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server. Tempdb database provides temporary space for various operations and is recreated every time SQL Server is restarted so always there is a clean copy of tempdb database when its restarted. Tempdb is a temporary database used by SQL Server to store temporary tables, table variables, cursors, work tables, row versioning. Operations within tempdb are minimally logged. This enables transactions to be rolled back. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb. Leaving the default configuration of tempDB is not ideal in many environments. Proper optimization of the tempdb database increases the performance of your SQL Server.

 Some of the best Practices for tempdb are: Read more…