What is TempDB Database in SQL Server?

The tempdb is a system database, which is global resource available to all users.

The tempdb is a temporary workspace for storing temporary tables, worktables that hold intermediate results during the sorting or query processing which intern increase the performance for SQL Server.

The tempdb is re-created each time the SQL Server service starts with clean copy of the database. The tempdb is created from model database and reset to its last configured size.

You can shrink tempdb but shrinking files regularly is not a recommended practice, because these files may probably grow again and also shrink operations causes’ data fragmentation. 

Backup and restoration operations are not possible on tempdb database.

Major Problems with TEMPDB:-
1. tempdb has run out of space.
2. tempdb is experiencing I/O bottleneck
3. tempdb is experiencing contention

In my next post i will document how to move TempDB database from one drive to another.

No comments:

Post a Comment