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.
…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)