SQL Server TempDb database

What is tempdb in sql server ?

Tempdb database used as system database for all users connected to sql server instance for temporary processing query result. It stores all temporary stored data result, temporary table and temporary stored procedures.

Whenever sql server starts, it creates new Tempdb for each sql server instance. All temporary data get dropped automatically after disconnecting seesion, Therefore data is vision to only active session at that time.

What is use of tempdb?

Basically used to hold temporary result generated by the query pertaining to each session in sql server. Also to hold internal worktable created by the sql server database engine.

By default, sql server tempdb databse is in simple recovery model and minimally logged. Backup and restore is not possible for tempdb database.

While installation of sql server tempdb database get created with default file location “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA” with tempdb.mdf as data file and templog.ldf as log file in sql server

Performance

  • If  Tempdb database size is very large then it might effect over all performance of sql server.
  • Always recommeneded to keep tempdb database in a separate disk to improve i/o operation for sql server.
  • Set tempdb database to a reasonable size to avoid autogrowth, frequently expanding of tempdb database can affect to sql server performance.

 

 

Leave a Reply

  

  

  


6 + = 15