SQL SERVER – Tricky Question – What is the Default Size of the Database

I love tricky questions – they are fun and educating. Yesterday I was presenting in one of the largest organization in India on SQL Server Performance Tuning Subject. During the conversation, one of the user suggested that every single time they are creating new database it is created with the big MDF file. They were wondering how come they have always very large file when they create new database. It was indeed a fun question to be asked.

In reply to the same question – I asked following question to the audience.

What is the default size of the SQL Database?

I got many different answers for the question. Here are few of the answers.

  • 3 MB
  • 4MB
  • Many other numbers….
  • As big as integer
  • 8060 bytes

…and so on and so forth. However, the real answer is as follows:

The default size of the user database is based on system database model.

When we create a new user database it is modeled after model database and created with same settings and properties. If your model database has large mdf or ldf file the same can be automatically replicated when a new user database is created. This was the actual case in the scenario mentioned earlier. On a separate note, with the default installation of SQL Server the mdf file of model database is 3 MB and ldf is about 1 MB.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – Tricky Question – What is the Default Size of the Database

  1. It depends if you are using GUI or TSQL to create the DB. When GUI is used the size of files will be the ceiling integer value (matching to MB). When done through TSQL this will be exactly match model. Now in case there is a TF that can skip off the empty space from MODEL while create a new DB…. that’s a twinkle.

Leave a Reply

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