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.
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.
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.
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.
SQLTwins by Nakul Vachhrajani 
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.
LikeLike
@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.
LikeLike
It is also important to note that the size, auto-grow settings and number of physical files associated with tempdb are independent from model.
LikeLike
**@opc.three** – the above example proves that at creation time, size of tempDB is not independent of the model database 🙂
LikeLike
@Nakul, please have a look at the ALTER DATABASE…MODIFY FILE command.
LikeLike
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 … )
LikeLike
**@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
LikeLike
thanks for the post
LikeLike
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………
LikeLike
@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
LikeLike
Thanks for the post it is very important
LikeLike
Thank-you, @raymondt. I am happy that you liked my post. Do keep sending in your feedback!
LikeLike
Pingback: SQL Server – TempDB v/s Model database – Minimum size considerations – CREATE DATABASE – Error Msg 1803 | SQLTwins by Nakul Vachhrajani