Default database file sizes

, 2016-07-12

T-SQL Tuesday 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.

AutoGrow1

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.

NewDB2016_2

Well what do you know! SQL 2016 not only goes faster it has lots of awesome new changes too.

New default changes

New database file defaults

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!

Change the Model database file defaults.

Change the Model database file defaults.

And now the default for new databases has changed.

And now the defaults for new databases have changed.

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Databases, T-SQL Tuesday Tagged: database files, default settings, microsoft sql server, model database, SQL 2016

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads