Five facts about the SQL Server Tempdb Database

By: Sergey Gigoyan | Comments (1) | Related: More > System Databases

Problem

The tempdb database is one of the SQL Server system databases. While many database specialists often have a chance to work with the tempdb database, I have noticed that there are many myths, misconceptions, and misleading opinions around this database even among DBAs and database developers. In order to disprove some common myths and provide information about some useful features of tempdb, we will cover five facts about this database in this article.

Solution

Below, we will provide facts and descriptions with detailed examples.

Fact 1 – We can see the SQL Server instance start date and time in tempdb

When a SQL Server instance is started, a new tempdb database is created. Therefore, the instance start date and time and the tempdb creation date and time are the same. So, if we want to know when the instance was last started, we can just check the tempdb database creation date.

We can do that in SQL Server Management Studio (SSMS) by right-clicking on tempdb and choose "Properties". In the "General" tab we can look at "Date Created":

The date mentioned there is the tempdb database creation date and also the instance start date.

Now, let’s do a simple test and restart the instance. We can do that in SQL Server Configuration Manager:

After the restart, let’s check the tempdb creation data again. As we can see, the date is changed and corresponds to the date when the instance was restarted. Hence, the tempdb creation date can be used to see when the instance was last started.

Fact 2 – Temporary tables and temporary stored procedures are stored in the tempdb

Despite some myths that temporary tables and table variables are stored in memory (RAM), in fact, they are stored in the tempdb database. Temporary stored procedures are also stored in tempdb.

Let’s do some tests and see how we can locate these temporary objects in tempdb. The script below creates local and global temporary tables, local and global temporary stored procedures:

--Local temporary table
CREATE TABLE #TestTable(ID INT, Val INT)
 
--Global temporary table
CREATE TABLE ##TestTable(ID INT, Val INT)
GO
 
--Local temporary stored procedure
CREATE PROC #getCurrentDate
AS
BEGIN
   SELECT GETDATE()
END
GO
 
--Global temporary stored procedure
CREATE PROC ##getCurrentYear
AS
BEGIN
   SELECT DATEPART(YEAR,GETDATE())
END
GO

After executing this script, we can find these objects in tempdb using SSMS.

Temporary tables and temporary stored procedures can be found under "Temporary Tables" and "Programmability" > "Stored Procedures" correspondingly:

So, this visual example above proves that these objects physically exist in the tempdb database.

Fact 3 – It's possible to create a regular table in tempdb

In tempdb, it is possible to create regular tables. If we run the code below, we can see that a regular table is added in tempdb:

--Regular table in tempdb
USE tempdb
GO
 
CREATE TABLE TestTable(ID INT, Val INT)
GO
 
INSERT INTO TestTable(ID, Val) VALUES(1, 100)
GO

In the SSMS, in tempdb, under "Tables" we can find our new table:

This table is available from other sessions (unlike local temporary tables), and it is not deleted automatically even if there are no connections to it (unlike global temporary tables). So, this table is a regular permanent table. But permanent? Hmm, up to a point, because if we restart the instance and run the query below again, we will get an error:

USE tempdb
GO
 
SELECT * FROM TestTable

This is because after the restart, a new copy of tempdb is created and all user-created objects are deleted. Therefore, we cannot find our TestTable even though we created it as a permanent table.

Fact 4 – It's not possible to back up the tempdb database

There is no option to back up and restore the tempdb database. This is reasonable as the tempdb database is not aimed at storing permanent information.

We can check this in SSMS. If we right-click on any other database in the SSMS and choose tasks, we can see that the "Back Up…" task is available:

If we do the same for the tempdb database, we can see that there is no "Back Up…" under "Tasks":

If we try to back up tempdb using T-SQL code, we will receive a very clear message that it is not allowed:

BACKUP DATABASE tempdb TO DISK = 'C:\MSSQL\tempdb.bak'

After running this code, we get this self-explanatory message:

Fact 5 – In the case of row versioning, the row versions are stored in the tempdb

When we use row versioning in SQL Server, tempdb is used to store the row versions. Particularly, if we set the READ_COMMITTED_SNAPSHOT option to ON in the READ COMMITED isolation level, row versioning is used instead of locking and the initial versions of rows are stored in tempdb.

In the SNAPSHOT isolation level also, the original versions of the rows are stored in tempdb (in SQL Server 2019, however, it is possible to store the initial versions of the rows in user databases).

On the one hand, using row versioning improves concurrency as the original data is available from the snapshots in tempdb. On the other hand, it is important to consider the fact that this approach can overload tempdb. Thus, storing snapshots for row versioning is another example of tempdb usage.

Conclusion

To sum up, we have provided five useful facts about the tempdb database. This information helps to avoid some common misconceptions about tempdb and can be very handy while working with this database.

Next Steps

For more information, please follow the links bellow:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Saturday, March 6, 2021 - 10:35:36 AM - Hassn	Back To Top (88351)
Perfect