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 (https://blog.sqlauthority.com)
5 Comments. Leave new
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.
Default size of the DB / file depends…. if you are using GUI it will be rounded to the ceiling (MB value) in case of TSQL it will be exactly like MODEL
4mb
3 MB by default /size of model database
Nice info. easy to remember also. Thanks.