Introduction
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
select
reserved_MB=(unallocated_extent_page_count+
version_store_reserved_page_count+
user_object_reserved_page_count+
internal_object_reserved_page_count+
mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024.,
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.,
user_object_reserved_page_count,
user_object_reserved_MB =user_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage
SQLZealots 