Five facts about the SQL Server Tempdb Database
By: Sergey Gigoyan | Updated: 2021-03-02 | Comments (1) | Related: More > System Databases
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.
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.
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.
For more information, please follow the links bellow:
- SQL Server TempDB Tutorial
- Microsoft Docs - TempDB Database
- SQL Server Global Temporary Table Visibility
- SQL Server READ_COMMITTED_SNAPSHOT Database Option and READ COMMITED Transaction Isolation Level
- READ_COMMITTED_SNAPSHOT and SNAPSHOT_ISOLATION levels in SQL Server
- Comparison of SQL Server Serializable and Snapshot isolation levels
About the author
View all my tips
Article Last Updated: 2021-03-02