In my last post Does the tempdb Log file get Zero Initialized at Startup? I questioned whether or not tempdb is actually created from the model database or not at startup. There is actually an easy way to prove that this statement, at least internally to the tempdb database is in fact TRUE. Many thanks go out to Bob Ward (Blog | Twitter) for pointing this out after trading emails with him.
To validate that tempdb is actually copied at startup from the model database, all that is necessary is to make a change to the model database, and restart the SQL Server database instance to validate that the change is in fact reflected in the tempdb database after instance recovery completes. To do this, I created a table in the model database and then created a row in the table using the following script:
USE [model]
GO
CREATE TABLE [SomeTable]
(RowID INT IDENTITY PRIMARY KEY)
GO
INSERT INTO [SomeTable] DEFAULT VALUES
GO
SELECT *
FROM [SomeTable]
Once this query has been run, and the instance has been restarted. to prove that the contents of the model database have in fact been copied into tempdb, all that is needed is to query the SomeTable table from tempdb.
USE [tempdb]
GO
SELECT *
FROM [SomeTable]
This will return one row which has a RowID of 1 which matches the information previously created in model, proving that tempdb, at least internally is recreated from model, inline with the KB article previously mentioned (See: KB 307487).
This still leaves a few questions about how tempdb is actually allocated when the physical files already exist, but proves that my previous statement is false:
So that leaves me asking, does SQL Server really recreate tempdb from the model database every time it starts? According to KB Article 307487 :
“When SQL Server starts, the tempdb is re-created by using a copy of the model database and is reset to its last configured size.”
It would certainly seem as if this statement is wrong based on testing.
The tempdb database is certainly created at least internally from a copy of the model database, but the question about how the physical file allocations occur still stands.