Rajendra Gupta
TempDB minimal logging

Relationships and comparisons between SQL Server TempDB and Model databases

August 17, 2021 by

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.

The below figure shows that the model database recovery model is full, but the TempDB recovery model is simple.

Recovery Model difference

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.

Modify recovery model

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:

As shown below, the TempDB database has four data files, while the Model database has one data file.

SQL Server TempDB data files and log files

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.

Check TempDB internal configuration

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:

Auto-growth configuration for TempDB files

Modified Auto-growth

Modified Auto-growth

You can change auto-growth using the following T-SQL statement as well.

We can verify that TempDB log file auto-growth is still set to 64 MB in the following figure.

Check TempDB log growth

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.

view log file auto-growth configuration

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.

Now, restart SQL Service and verify whether the [AuditLogs] table exists in the TempDB database. The table exists as demonstrated below.

Database objects

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:

Which database starts first ?

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.

SQL Server TempDB messages 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.

Backups for TempDB

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.

Backup error

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.

As shown below, the query takes 2 seconds to complete.

Create SQL Table

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.

TempDB minimal logging

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.

Rajendra Gupta
General database design, Installation, setup and configuration

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

187 Views