SQL Server – What is tempdb?

 

Abstract Cube Array Red Blue Green

Temp database is a global resource for all users connected to an instance. We are not using tempdb directly, but we are using tempdb while creating temp tables, cursors, rebuilding indexes etc. Today I am going to explain in detail about tempdb. Tempdb is mostly like a user database in physical level i.e. it contains two files ldf and Mdf.

Tempdb is in simple recovery model, which means it is minimally logged.

 

Note: Tempdb is created each time when SQL Server instance is restarted. So there is no need for moving tempdb to another server. We can change the location of the tempdb files such as ldf and mdf to new location using the command

 

USE master;
 
GO
 
ALTER DATABASE tempdb
 
MODIFYFILE(NAME = tempdev, FILENAME = 'C:Programfiles\SQL Server \SQL Files\tempdb.mdf');
 
GO
 
ALTER DATABASE tempdb
 
MODIFYFILE(NAME = templog, FILENAME = 'C:Programfiles\SQL Server \SQL Files\templog.ldf');
 
GO

 

What does tempdb consists of?

 

  • When we are creating temporary tables, global temporary tables, cursors.
  • Tempdb is used by the SQL Server database engine, for creating work tables for doing sorting operations such as Group by, Union etc.
  • Row versioning that are generated by data modifications transactions in a database.
  • Row versioning that are generated by data modification such as online index operations, Multiple Active result sets and After triggers.