Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2008 R2 and creating databases through continuous deployment.

In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we have a legacy application that prevents us from changing the schema much. We would like the database settings to be configured at the instance level so we can change it for staged deployments.

I have read in several places that default settings for a new db are based on the settings for 'model', but it appears this only works by clicking new database in SQL Management studio UI and not from a script e.g. CREATE DATABASE [MyDb].

This question came from our site for professional and enthusiast programmers.

5

If you're scripting it out with CREATE DATABASE why can't you just specify it in your script?
–
JNK♦Jul 31 '12 at 17:17

1

@JNK I think he wants it to inherit rather than having to check what it should be. And it should do that, but I have to agree that it doesn't (and it is not doing that in 2012 either). I actually didn't believe it until I tried it - I am sure this worked correctly at some point. Maybe a regression from bug fixes for that 10000% thing.
–
Aaron Bertrand♦Jul 31 '12 at 18:48

In that case are those figures available in a DMV somewhere? could you possibly script it out by querying system tables or DMVs for appropriate values?'
–
JNK♦Jul 31 '12 at 18:54

To clarify, We would rather inherit. We have a large number of db servers with varying data storage across our staged environments and controlling script changes per environment would be more work at this stage. @JNK's idea is one I hadn't thought of. It looks like there may be a query that will help: beyondrelational.com/modules/2/blogs/28/posts/10326/…
–
Michelle SteeleAug 1 '12 at 9:37

ps. more info on this table here: msdn.microsoft.com/en-us/library/ms181338.aspx. In my code above I haven't accounted for the maxsize=0 case (i.e. no growth), and have masked the status code since I had a different figure for percentages to what's in the documentation, but was able to make the values comparable via masking. I'm not worried about the 0 status code, since that's taken care of by maxsize (were it implemented).
–
JohnLBevanAug 13 '12 at 14:00