This article explores relationships and comparisons between SQL Server TempDB and Model databases in a unique way.
Introduction
By default, each SQL Server instance comes up with four system databases – Master, MSDB, Model, and TempDB. The TempDB database is a global database that is used by almost all users and processes for various functions such as storing temporary objects, internal objects, Intermediate sort results for certain operations (Group BY, Order BY, rebuilding index)
This article will explore some helpful information about this database.
SQL Server TempDB & Model database#1: TempDB is a copy of the model database?
The model database in SQL Server acts as a template for all newly created databases in SQL Server. If you make any changes to this database, all new databases get those changes. Now, Most of the DBA knows that SQL Server recreates the TempDB upon Service restart. Therefore, do not use TempDB to store your objects in the TempDB database.
Therefore, SQL Server also uses a model database to create the TempDB database. However, it is not an exact copy.
SQL Server TempDB & Model database#2: Recovery Model difference
The model database sets the default recovery model of new databases.
|
1 2 3 |
Select [name] as Databasename, recovery_model_desc as DBReocoveryModel from sys.databases where name in('TempDB','Model') |
The below figure shows that the model database recovery model is full, but the TempDB recovery model is simple.
Let’s try restarting SQL Server and rerun the above query. You get the same results. TempDB always remains in the simple recovery model because it is a minimally logged database. You cannot modify the recovery model to full or bulk-logged. Else, it gives the following error message.
|
1 2 |
USE [master] ; ALTER DATABASE [TempDB] SET RECOVERY FULL ; |
Conclusion: TempDB database always remains in a simple recovery model. The model database recovery model does not impact it.
SQL Server TempDB & Model database#3: TempDB data files and log files
By default, each new database gets one primary data file (*.mdf) and the transaction log file (*.ldf). As per best practice, DBAs configure the multiple TempDB database files according to the number of logical processors to reduce contention.
- The number of logical processors is less than or equals 8, create the data files as per logical processors
- The number of logical processors is greater than eight, create eight data files
- Monitor TempDB contention and, if required, increase data files by multiples of four
Suppose you created four TempDB data files for your SQL instance. As we know, by default, the Model database has only one data file. Therefore think of a few questions:
- Do you need to recreate TempDB files each time you restart SQL Server?
- Does the TempDB number of files equal the Model database data files?
In my demo environment, I have one TempDB data file. Therefore, I added three more files using the following query:
|
1 2 3 4 5 6 7 8 |
USE [master] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdev3.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdev4.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO |
As shown below, the TempDB database has four data files, while the Model database has one data file.
Now, restart the SQL Services using SQL Server Configuration Manager and verify the number of TempDB data files. You get the four TempDB data files that were configured before SQL Service restart.
SQL Server stores the number of data files and log files in its internal tables. For example, the below query retrieves data from the [sys].[master_files] for the TempDB configuration.
|
1 2 3 |
select db_name(database_id) as [DatabaseName], type_desc, name, physical_name, size, growth from [Master].[sys].[master_files] where db_name(database_id)='TempDB' |
Conclusion: SQL Server preserves the number of TempDB data files after the SQL Service restart.
SQL Server TempDB & Model database#4: Auto-growth configuration for TempDB files
Suppose you have configured the TempDB log file to auto-grow as 128 MB in your SQL instance.
Default configuration:
Modified Auto-growth
You can change auto-growth using the following T-SQL statement as well.
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', FILEGROWTH = 131072KB ) GO |
We can verify that TempDB log file auto-growth is still set to 64 MB in the following figure.
Let’s restart SQL Services and view log file auto-growth configuration. Even after the restart of SQL Services, TempDB gets the configuration that was before service restart.
Conclusion: TempDB does not get the auto-growth configuration from the Model database.
SQL Server TempDB & Model database#5: Database objects in Model and TempDB database
As we know, the model database is a template for the newly created database. Therefore, you should not create any object in it until you want all new databases to have a copy of those objects. Suppose you created an audit table in the model database.
Does the TempDB get objects from the model databases?
To answer the above question, let us create an object in a model database using the following script.
|
1 2 3 4 5 6 7 |
Use Model go Create Table AuditLogs ( ID int, Auditdata varchar(50) ) |
Now, restart SQL Service and verify whether the [AuditLogs] table exists in the TempDB database. The table exists as demonstrated below.
Conclusion: If you create any objects in the model database, it gets created in the TempDB database and service restart acts similar to a new regular user database
SQL Server TempDB & Model database#6: Which database starts first – Model or TempDB?
Once SQL Service starts, it follows a specific order for bringing all databases in the online status. It logs an entry into error logs for the starting database and their recovery process.
To get the database startup sequence, execute the following query:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @DBS TABLE ( LogDate DATETIME, ProcessInfo VARCHAR(10), LogText VARCHAR(50) ) INSERT INTO @DBS (LogDate, ProcessInfo, LogText) EXEC sys.sp_readerrorlog 0, 1, 'Starting up database' SELECT dbs.LogDate LogDateTime, dbs.ProcessInfo, dbs.LogText FROM @DBS AS dbs ORDER BY dbs.LogDate ASC |
As shown in the above screenshot, SQL Server first starts the Master database, followed by the resource database (mssqlsystemresource). The TempDB starts only after the model database. Therefore, the model database is essential for SQL Service startup. If it is not accessible, SQL Service won’t start.
If you filter the error log specifically for TempDB, you get the following entries. During TempDB startup, it also logs the number of TempDB files in the error log.
Conclusion: Model database is essential for TempDB during service startup. You can also find out the number of TempDB files from the error log
SQL Server TempDB & Model database#7: Does TempDB database backup work similar to a Model database
The model database backup depends on the recovery model. If DB is in default (full) recovery model, you can take full, differential, log backup similar to a regular transactional database.
The TempDB database always remains in the simple recovery model. However, you cannot take a backup of any kind for the TempDB. In the SSMS GUI, you do not get an option for backup for TempDB, similar to a model database as shown below.
If you try to take a DB backup using the T-SQL, you get the following error message. It terminates the backup starting it is not allowed on TempDB database.
SQL Server TempDB & Model database#8:Transaction logging in Model and TempDB database
SQL Server uses minimal transaction logging for the TempDB database. Therefore, if you run a workload on a regular user database or TempDB, the performance will vary. If we restart SQL Service, we get a clean copy of TempDB. Hence, TempDB logs only certain log records for a transaction.
On the other hand, the model database is a template database; therefore, SQL Server ensures the transactions are logged even in the simple recovery model to perform a rollback.
To check the transaction performance, we will initiate the same transactions on both the Model and TempDB databases.
- Note: Do not use a model database for executing the query. In this article, we use it for performing the demonstration of Model and TempDB relationships
The following query creates [DemoTable] in the TempDB database and inserts few records into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Use TempDB Go CREATE TABLE DemoTable( ID INT IDENTITY, [Date] DATETIME DEFAULT GETDATE(), [Text] varchar(500) default 'ABC'); SET NOCOUNT ON; declare @i int = 0 while @i<100000 begin INSERT INTO dbo.DemoTable DEFAULT VALUES; set @i=@i+1 end |
As shown below, the query takes 2 seconds to complete.
Similarly, execute the same query on the Model database, and it takes 13 seconds. Similarly, the query took 2 seconds in TempDB earlier. It is due to minimal transaction logging in the TempDB.
Conclusion
This article explored the relationship between Model and SQL Server TempDB databases. Usually, DBAs do not consider the significance of a model database as it is simply a template for the new database. It plays an essential role in SQL Server database creation. The TempDB database is also a copy of it inheriting few properties like the number of files, auto-growth, recovery model from the master database tables.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023