Default database file sizes5
July 12, 2016 by Kenneth Fisher
It’s Chris Yates (b/t) birthday! (I think he just tured 19.) And in honor of his birthday we are writing blog posts! Well, technically it’s T-SQL Tuesday again, but we can pretend we are writing for Chris’ birthday. In fact that’s the subject he’s selected. “Give yourself a birthday present.” Since my birthday is in October I’m just going to call this a birthday present for Chris.
Default database file sizes have been updated!
The other day I noticed there was an #AskSQLTiger hash tag on twitter, so I thought I would ask about database defaults. For years they have been pretty unrealistic.
No one is going to use a file size of 3mb (data) and 1mb (log) unless it’s a very very simple test. And the autogrowth of 1mb (data) and 10% (log) has caused me more grief than you would believe. I mean by the time you are working with multi gb files these growth patterns just don’t work anymore. Just think about the number of growths at 1mb at a time it would take to get to a 10gb data file.
The Tiger team responded.
Well what do you know! SQL 2016 not only goes faster it has lots of awesome new changes too.
Now 8mb initial 64mb growth still isn’t all that big. Perfect for testing, or small lab environments, but not big enough for live databases in my opinion. But guess what? The awesome doesn’t stop here!
The default file sizes didn’t used to come from the Model database. This is no longer true!
To be fair when you are creating a new database you should pay attention to file sizes and file growth. On the other hand mistakes happen, people are lazy, etc. It’s nice to know that the safety net (defaults) have been updated to be a bit more realistic.
Category: Microsoft SQL Server, SQLServerPedia Syndication, System Databases, T-SQL Tuesday | Tags: database files, default settings, microsoft sql server, model database, SQL 2016
5 thoughts on “Default database file sizes”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Great post, always good to check on your file growth sizes.
Absolutely. I’m really excited that they finally changed these!
While we do have some larger databases not all of them are in the multi-terabyte range. I hope that someone has left the minimum file size smaller than that so I don’t have a bunch of WASTED disk space on some of the utility databases we have in our systems. For example, we have a db that essentially serves as a traffic cop to the web server – giving directions to where the data a particular application is looking for lives in the systems. That database is only 3 MB.
Just remember that the gargantuan size databases are still not the norm for everyone. If you want to have a larger db on creation perhaps you should learn to create your databases with scripts rather than a GUI.
No worries 🙂 The new default DB file sizes are 8MB each with a growth of 64MB each. And while my example above was changing those defaults (by changing model) to starting at 1GB each (with a growth of 512MB) you could just as easily reduce them to something lower.
16mb for a small database seems pretty reasonable to me though. Even if your DB is only going to use 3mb you aren’t exactly wasting much space.
Thanks for the comment!
[…] Kenneth Fisher – Default Database File Sizes […]