TempDB Recreation Myth!

The estimated reading time for this post is 1 minutes

Most SQL Server DBAs believes that TempDB always recreated once SQL Server service is restarted and makes a clean copy of TempDB database. therefore, it came to my mind if we configure TempDB data file size to 120GB and log file to 45GB, we might have some performance issue in case of instance restart or failover in cluster environment.

Okay, we know that ‘Perform Volume Maintenance Task’ in Group Policy can change the file creation behavior but it does not apply on log file (LDF) and SQL Server writes entire log file with ‘0x0’ value in prior SQL Server 2016 and ‘0xc’ value in SQL Server 2016 to make sure the log parity is there.

I did some experiment and I found that TempDB does not recreate during instance restart. based on my experiment, SQL Server only creates the TempDB data and log files if the files are not exists, otherwise SQL Server just clear the Boot, GAM, SGAM, IAM, PFS and other main data pages and also restart the LSN value from beginning.

Lets Do Experiment Together!

First, lets stop the SQL Server instance and Shift+Delete the TempDB database files as per following figures:-

As you can see, each database file is almost 8GB and for Log file, it takes long time to create because SQL Server needs to write ‘0x0’ value into the file in prior SQL Server 2016 versions and ‘0xC’ in SQL Server 2016.

The SQL Server is currently stopped and TempDB database files are delete permanently. Now its time to start the SQL Server instance and observe the engine behavior and startup period from Error log file.

As you see in above figure from error log, it shows that creating the TempDB database and bring it up took about 32 seconds. Now, lets stop and start the SQL Server instance and check the behavior and startup period time.

The above figure shows, SQL Server only takes less than 10 milliseconds to start up the TempDB database! why? because it only clear some of the data pages as mentioned above.

Conclusion

Do not worry to set the TempDB data and log file size quiet huge due to SQL Server behavior on other user databases. SQL Server always treats TempDB nicer than other databases. (haha…)

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

6 Comments on "TempDB Recreation Myth!"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
Ravi Theja Madisetty
Guest

Hi,
After restarting Sql services, still temp db files showing same size. Before this we used to shrink the temp db data and log files by using DBCC command ?

Do you have any clue on this ?

Best Regards,
Ravi Theja Madisetty

Thayal Muhunthan
Guest

What happens when cluster fail over from Node A to B ? TempDB is sitting on SAN with user DBs. Will it get re-created on Node B or the files are moved from A -> B ?

wpDiscuz