Tag: TEMPDB in SQL Server

TEMPDB – the most important system database in SQL Server


If you had anytime worked in SQL Server, you would be knowing about a system database – TEMPDB. TEMPDB is one of the most important system databases in SQL Server. As the name denotes TEMPDB database is a temporary database to SQL Server to do certain operations. As the topic is vast and its impossible to cover all aspects of TEMPDB in a single blog, planning to cover in multiple blogs to explain about TEMPDB.

Characteristics of TEMPDB

    1. TEMPDB is automatically created/recreated every time you start/restart your SQL Service That means, data anything reside in the tempdb is not persistent forever. If the service has been restarted, all the data will be lost.

    2. Without TEMPDB, you will not be able to start your SQL Server

    3. TEMPDB cannot be dropped or created manually

    4. TEMPDB cannot be backup up

    5. TEMPDB can have ONLY on SIMPLE recovery model

    6. TEMPDB can have only one filegroup(PRIMARY Filegroup)

    7. TEMPDB is a shared database

    8. TEMPDB never stored redo information, That means, you can ONLY rollback with help of undo information

    9. TEMPDB databse cannot be take to OFFLINE

    10. DBCC CHECKALLOC and CHECKCATALOG cannot be issued

Usages of TEMPDB

TEMPDB has been used in SQL Server for various purposes.

    1. Global/Temporary tables/Temporary procedures are created in TEMPDB (more details)

    2. Table Variable (more details)

    3. Cursors and internal objects created by DB engine to store intermediate results

    SQL Server database engine will create internal work tables as required as per the instruction from the relational engine. This is to improve the performance of operations like sorting, spooling, hashing etc.(more details)

    4. Online index operations needed TEMPDB

    5. Snapshot and Read Committed Snapshot Isolation requires TEMPDB

    6. Triggers

    7. Version store

I am sure this not an exhaustive list, If I miss anything, please feel free to add as comments.

How to understand the usages of TEMPDB

			mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024., 
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.,
user_object_reserved_MB =user_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage

See Also

Whats new with TempDB in SQL Server 2016

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!