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:-
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.
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…)