TempDB is an instance-wide Temporary workspace that SQL Server uses for various operations. Tempdb is created from Model Database whenever the SQLSERVER service is started/restarted. TempDB has simple recover model. It is a rule of thumb that you should never keep tempdb in the same location where your user database is stored. It is always recommended that tempDB should be located in a high-performing disk subsystem (preferably RAID 10 or RAID 0). It is also to be configured with enough size for better performance, so that system does not need to do the space allocation dynamically. You can also create multiple TempDB data files to minimize IAM and SGAM and thereby improve the performance.

Very common operations which consume tempdb are appended below:

  • Store explicitly created Temp tables, Table variables, Stored procedures, cursors etc.
  • Stores Private and Global variables used throughout the instance.
  • Worktables associated with ORDER BY, GROUP BY, CURSOR.
  • Many System administration activities like DBCC commands use TempDB.
  • If you have chosen Snapshot Isolation Level then it uses TempDB.
  • If your Report Server Uses Caching, then tempdb is used extensively.
  • If your have used SORT_IN_TEMPDB option while rebuilding indexes.

To relocate TempDB Follow these steps:

  • Alter the file path by following this command.
    ALTER database tempdb MODIFY FILE (NAME = tempdev, FILENAME= 'E:\Sqldata\tempdb.mdf')
    ALTER database tempdb MODIFY FILE (NAME= templog, FILENAME= 'E:\Sqldata\templog.ldf')
  • Restart the service and run sp_helpfile tempdb to confirm the above action.


Tempdb in Sql Server 2005

How to shrink the tempdb database in SQL Server - Microsoft

KB328551 - Concurrency enhancements for the tempdb database - Microsoft

Copyright © 2024 delaney. All rights reserved.