Let us take a closer look and discuss TempDB files in SQL server with our MSSQL support services. We Bobcares respond to all your queries regarding SQL servers, no matter how minor.

TempDB files in SQL server

Any SQL Server (or Azure SQL/Amazon RDS SQL Server) you work on will have a TempDB, and it will always have a Database_ID of 2. TempDB in SQL Server performs a number of functions to support both system and internal operations.

With this workload, TempDB will process a large number of database writes, which requires low latency, and high throughput underlying storage. Beyond that, there can be contention for busy system pages in TempDB, so creating multiple TempDB database files is a best practice to improve throughput.

Since SQL Server uses a proportional fill mechanism all TempDB data files should be sized equally.

The Page File for the Database

When you execute a query that joins data to another table with an ORDER BY command, the database engine allocates a certain amount of memory for that joining operation. The amount of memory granted is based on the column and index statistics, which means there are a lot more records than the engine expects and there will not be enough memory to complete the operation.

It uses TempDB to complete the operation since SQL Server is in the business of returning your query results and not throwing errors,

What is the purpose of SQL Server TempDB?

One of the functions of TempDB is to perform like a page or swap file at the operating system level. If a SQL Server operation is too large to be completed in memory or if the initial memory grant for a query is too small then the operation can be moved to disk in TempDB.

Another function of TempDB is to store the temporary tables, anyone who has created a temporary table in T-SQL using a hash prefix (#) or the double pound/hash prefix (##) has created an object in TempDB as is where those are stored.

CREATE TABLE #MSSQLTips (RowID int);

• When a trigger is executed the inserted and deleted virtual tables are stored in TempDB.

• Any databases that use “READ COMMITTED SNAPSHOT ISOLATION” will have their row versioning data stored in TempDB.

• In a few ways, TempDB can also be called explicitly. Tables generate in TempDB by referencing the database in a creative statement.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

Upon every start of the SQL Server instance, TempDB is regenerated. Any objects that may have been created in TempDB during a previous task will not persist upon a service restart. TempDB acquires its initial object records from the model database which is generally going to be empty or nearly empty.

Index maintenance can alternatively be asked to do sorts in TempDB rather than attempting to do so using free space in the user database.

ALTER INDEX [PK_numbers] ON [dbo].[numbers] REBUILD WITH (SORT_IN_TEMPDB = ON);

Can SQL Server run without a TempDB database?

SQL Server cannot run without a TempDB – except in very extreme recovery scenarios and for very short periods.

Can you drop or rename the TempDB database?

TempDB will not detach, dropped, taken offline, or renamed. However, attempting any of these operations will return an error. As this is a critical system database there is no reason that this should ever be attempted.

What physical files and name extensions support the TempDB database?

The default logical filenames are tempdev and templog, to which tempdev is for data and templog for log, and the disk is tempdb.mdf and templog.ldf respectively. TempDB commonly has many data files.

How many TempDB data files should there be?

There can be one TempDB data file for each thread/core/vCPU on the instance with a maximum of 8.

Where to find the TempDB database on disk and in SSMS?

The files can be found by querying sys.sysfiles DMV or the file pane on the database properties window.

SELECT * FROM TempDB.sys.sysfiles;

[Looking for a solution to another query? We are just a click away.]

Conclusion

TempDB is a database that has many functions within SQL Server and it is rarely called explicitly. It has so many functions that it is often one of the busiest databases of SQL Server instances.