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 nakulvachhrajani.com (@SQLTwins)
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

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 nakulvachhrajani.com (@SQLTwins)
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.

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 nakulvachhrajani.com (@SQLTwins)
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:

• model database – http://msdn.microsoft.com/en-us/library/ms186388.aspx
• tempdb database – http://msdn.microsoft.com/en-us/library/ms190768.aspx
• ALTER DATABASE File and Filegroup Options – http://msdn.microsoft.com/en-us/library/bb522469.aspx
• CREATE DATABASE – http://msdn.microsoft.com/en-us/library/ms176061.aspx

Until we meet next time,

Be courteous. Drive responsibly.