THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Does tempdb Get Recreated From model at Startup?

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.

Published Friday, May 14, 2010 7:32 AM by Jonathan Kehayias

Comments

 

James Luetkehoelter said:

My understand (untested) was this:

1) Model does need to exist for TempDB to be created (or at least there's a check for it on startup)

2) TempDB will take on the properties of TempDB (file allocation only, no objects) if TempDB has not been modified

3) If modified, TempDB will essentially act like a user database on startup (although all temporary objects are cleaned out if left orphaned)

Does that jive or am I way off - I've never actually explored the details of this...maybe I will

May 16, 2010 6:02 PM
 

KKline said:

Good post, Jonathan.  Thanks for testing the assumptions!

May 18, 2010 3:34 PM
 

The Rambling DBA: Jonathan Kehayias said:

If you follow me on Twitter ( @SQLSarg ) you probably saw tweets about a bug I encountered in SQL Server

July 7, 2010 1:39 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement