SQL Server – TempDB – Is it a copy of the Model database?


Microsoft SQL Server comes with a whole range of system databases, which I visited in my post – How to programmatically identify system and user databases on a SQL Server instance. System databases are special, and there some very specific and peculiar characteristics of each (documentation for which can be found from Books On Line here).

Model v/s TempDB

One such system database is the “model” database. As the name suggests, user databases created on the particular SQL Server instance are copies of the model database.

The TempDB on the other hand, is a special system database. It’s special because every time the SQL Server is restarted, the TempDB is re-created, and we receive a clean copy of the TempDB. This got me thinking:

  • How does SQL Server recreate TempDB?
  • Does it copy the Model database and use that as the base?
  • How is it that all but one of the restrictions on the Model database are also applicable to the TempDB?

The only way to answer these questions, is to run a small test.

WARNING: The tests demonstrated in this post involve changing your default SQL Server installation. Please check with your SQL Server Administrator before performing these steps in your development and quality assurance environments. These tests should not be performed on a production database.

Modifying the Model database

On my test system, the model database is a clean one – no modifications have been made after SQL Server was installed. On such a clean model database, let’s create a table, and fill it with some test data.

USE model
GO
CREATE TABLE DefaultTableTest
( DataId INT IDENTITY(1,1),
  RandomId INT DEFAULT (RAND()*1000),
  DataName VARCHAR(10) DEFAULT 'DEFAULT'
)
GO

/******************************************
   Insert some test data into the table
******************************************/
USE model
GO
INSERT INTO DefaultTableTest DEFAULT VALUES
GO 600000

Now, let’s restart the SQL Server service. Take care to restart the service using the steps mentioned in my post here. Do not use the Services.msc console to restart Microsoft SQL Server.

image

Check the TempDB

Once the SQL Server restart is complete, it’s time to check the TempDB. First of all, let’s navigate to the physical drive where TempDB is located. We all know that typically, TempDB is 8MB in size upon SQL Server restart by default.

image

Because TempDB is larger than expected, it definitely has something more than usual.

Next, let’s refresh the Object Explorer pane in SSMS. We see that under TempDB, we have the DefaultTableTest table created for us. Therefore, let’s attempt to SELECT from the table.

image

Conclusion

TempDB is a copy of the model database – just as is the case with any other user database. No other system database is a copy of the model database – which adds to the reasons why TempDB is special.

There is also a lesson to be learnt from this. Whenever a change is made to the “model” database, one needs to be very careful about the change – it will directly impact one of the most used databases – the TempDB.

I hope you liked today’s post. Leave your feedback before you go.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

12 thoughts on “SQL Server – TempDB – Is it a copy of the Model database?

  1. SQLZealot

    Hi Nakul, Good information. Thanks for the posting. Could you please say something on the recovery model? I guess it will not be copied from model and uses only simple recovery.

    Like

    Reply
  2. Nakul Vachhrajani

    @SQLZealot: Glad that you liked it. Thank-you for taking the time out to read my post, and providing your feedback.

    Your thoughts are correct. After being copied over from the model database, tempDB does undergo a couple of transformations – one of them being the change to SIMPLE recovery model, and subsequent restriction on the user to change it.

    Like

    Reply
  3. opc.three

    It is also important to note that the size, auto-grow settings and number of physical files associated with tempdb are independent from model.

    Like

    Reply
  4. Nakul Vachhrajani

    **@opc.three** – the above example proves that at creation time, size of tempDB is not independent of the model database 🙂

    Like

    Reply
  5. Patrick Lambin

    Excellent article . A little question about differences between the model and tempdb databases : i think that a backup/restore is possible for the model database, but i am not sure that it is possible for the tempdb database.Moreover, it is useless as the tempdb is recreated nearly empty every time the SQL Server service is restarted.But i may go wrong … )

    Like

    Reply
  6. Nakul Vachhrajani

    **@Patrick Lambin:** In fact, you are absolutely correct. Backup/Restores on TempDB are not allowed. You can read more on the limiations imposed on the TempDB database in BOL at: [http://msdn.microsoft.com/en-us/library/ms190768.aspx][1], which has also been referenced in the post.

    **@opc.three:** I plan to conduct a small test on TempDB this week-end. I will let you know my findings once ready.

    **All:** I am happy to note that you liked the post, and am looking forward to your continued support. I really appreciate the feedback, so keep it coming!

    [1]: http://msdn.microsoft.com/en-us/library/ms190768.aspx

    Like

    Reply
  7. Bchopra

    Hi Nakul,

    I am facing a problem. I have a procedure. When i execute it in query analyzer, it execute properly but it get fails when i schedule it in job. Earlier it was was wokring fine in job. but every month i need to do some changes in the procedure due to month change. after changing that now it is not working in job, but working fine with query analyzer. I don’t know the exact problem, but might be of ansi settings. Please help………

    Like

    Reply
  8. Nakul Vachhrajani

    @Bchopra: I would be happy to help. However, can you post your stored procedure and sample code that you use to call the stored procedure (via the Query Analyzer and via the SQL Agent job) in the Ask module at BeyondRelational.com? (My Ask Page: [http://beyondrelational.com/ask/nakul/default.aspx][1])?

    The Ask module is specifically designed to help resolve problems or queries and discuss them out. Thanks!

    [1]: http://beyondrelational.com/ask/nakul/default.aspx

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s