SQL Server – TempDB v/s Model database – Minimum size considerations – CREATE DATABASE – Error Msg 1803


A few weeks ago, I wrote a post related to the tempdb where I attempted to answer the question – Is TempDB a copy of the model database? The post attracted a couple of follow-up comments (which I really appreciate, because it gives everyone a great deal to think about) and led me to a thought-filled week-end.

If you have not read my previous post, I strongly recommend that you read it by going to the link here – SQL Server – TempDB – Is it a copy of the Model database?

SCHEMA v/s Storage – Influence of the model database

Most administrators often make changes to their SQL Server database configuration. These changes typically include (and are not limited to):

  • Changing the default sizes and growth values of the data and log files
  • Changing the number of files & file-groups associated to a database
  • Changing the location of their database files
  • Changing the database collation
  • Implement mirroring, log shipping, etc
  • As demonstrated in my previous post, objects can be created in the model database, so that they are available whenever a database is created
    • Because the tempdb is re-created every time the SQL Server restarts, this is applicable to the tempdb also

Most of these changes can be classified into the following major categories:

  • Storage Parameters (size & number of files, collation, etc)
  • Security (changing roles and permissions)
  • Availability & Disaster Recovery (mirroring, log shipping, etc)
  • Schema changes (Defining or manipulating objects)

Each of these changes need to be applied either during database creation or after. These changes therefore need to be persisted somewhere so that they can be picked up during database creation/recovery.

However, the basic schema of any new database within Microsoft SQL Server will always come from the model database, and as demonstrated in my original post this is also applicable to the tempdb. Hence, while any database is schematically a copy of the model database, the storage & other parameters may differ based on the values specified by the user.

Is the initial size of a database independent of the model database?

This question is a tricky one to answer. So, let’s do a simple, 2-part experiment. First, let’s bring our environment up to the state mentioned in my previous post – SQL Server – TempDB – Is it a copy of the Model database?. This can be done by running the following script and restarting the SQL Server instance. Notice that as per the referenced post, tempdb gets copied over from the model database.

/**********************************************
              !!!WARNING!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND BEYONDRELATIONAL.COM
ARE NOT RESPONSIBLE FOR ANY DAMAGE CAUSED 
BY USING THIS SCRIPT.
**********************************************/
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

image

Part 01 – Influence of the model database’s size on the tempdb

Now, remove the test object from both the model and the tempdb databases. Also, let’s use the ALTER DATABASE…MODIFY FILE clause to change the default size of the tempdb.

/**********************************************
              !!!WARNING!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND BEYONDRELATIONAL.COM
ARE NOT RESPONSIBLE FOR ANY DAMAGE CAUSED 
BY USING THIS SCRIPT.
**********************************************/
--Remove the object from the model database
USE model
GO
DROP TABLE DefaultTableTest
GO

--Remove the object from the tempdb database
USE tempdb
GO
DROP TABLE DefaultTableTest
GO

--Alter the tempdb to use an initial size of 10MB as opposed to the current size (approx. 21MB)
ALTER DATABASE tempdb
    MODIFY FILE ( NAME = 'tempdev',
                  SIZE = 10MB
                )
GO

Let’s restart the SQL Server instance, and verify the size of the tempdb.

image

As you can see, the tempdb continues to be of the same size as the model database. So what went wrong? Well, what’s wrong is that ALTER DATABASE…MODIFY FILE cannot modify the size such that it is smaller than the current size. Per Books On Line (http://msdn.microsoft.com/en-us/library/bb522469.aspx): “If SIZE is specified, the new size must be larger than the current file size.

Now, let’s see if CREATE DATABASE can create a database for us that has an initial size which is less than that of the model database.

Part 02 – Influence of the model database’s size on a newly created database

We will pickup from where Part 01 left off, i.e. the objects in the model database are gone, and both the model and the tempdb are about 21MB each.

Let’s try to create a database by specifying an initial file size which is less than that of the model database:

/**********************************************
              !!!WARNING!!!
THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
WARRANTY. THE AUTHOR AND BEYONDRELATIONAL.COM
ARE NOT RESPONSIBLE FOR ANY DAMAGE CAUSED 
BY USING THIS SCRIPT.
**********************************************/
USE [master]
GO

CREATE DATABASE [InitialFileSizeDefined]
ON PRIMARY (NAME = 'InitialFileSizeDefined_Data', 
            FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATAInitialFileSizeDefined_Data.mdf',
            SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)
LOG ON (NAME = 'InitialFileSizeDefined_Log', 
        FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11.DENALICTP03MSSQLDATAInitialFileSizeDefined_Log.mdf',
        SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB)
GO

We receive the following error:

Msg 1803, Level 16, State 1, Line 2

The CREATE DATABASE statement failed. The primary file must be at least 21 MB to accommodate a copy of the model database.

Per Books On Line (http://msdn.microsoft.com/en-us/library/ms176061.aspx): “The size specified for the primary file must be at least as large as the primary file of the model database.

Conclusions:

The best part of the day was to summarize my thoughts. We can draw the following conclusions from the above observations:

  1. While every database (including the tempdb) is schematically a copy of the model database, the storage & other parameters may differ
  2. The minimum size of the any database has to be greater than or equal to that of the model database
    • ALTER DATABASE…MODIFY FILE and CREATE DATABASE cannot be used to circumvent this
  3. Extreme caution should be exercised when making changes (schematic or other) that result in a change to the size of the model database

The conclusion #2 is, I believe, by far the most important conclusion of this experiment. I look forward to hearing your thoughts on this topic.

References:

All references today come from Books On Line:

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Advertisements

2 thoughts on “SQL Server – TempDB v/s Model database – Minimum size considerations – CREATE DATABASE – Error Msg 1803

  1. benstaylor

    Nicely written.

    I actually came to your blog based on the topic…most developers aren’t even aware that tempdb is instantiated based on the current Model db configuration.

    I have never had a model database with large file sizes because that is something I always specify when creating a database. As a result, tempdb has also never been impacted in size based on Model, because I have always required a fairly large tempdb. With model specified at 1meg for data and 1meg for log, it will always be overridden.

    Thanks for taking the time to post references for your conclusions as well. That’s some real gold.

    Ben

    Like

    Reply
  2. Nakul Vachhrajani

    Hello!

    I am glad that you liked the post & the references that go with it.

    With respect to your point about most developers being unaware about the workings of this “magical” database called tempdb – that was exactly what prompted me to write both my posts on the topic. It’s so interesting and logical that it’s just too obvious. And as we all know – what is too obvious is the most difficult to see and comprehend.

    Once again, thank-you for reading. I hope you like all my previously posted and future blogs.

    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