August 23, 2016 at 8:14 am
Hi,
I know I've placed this under SQL Server 2012, but my understanding is this applies to SQL 2005 through to SQL 2014.
I'm wondering if it's possible to access and change the instance default values for database data and log file auto-growth settings. When a SQL Server instance is installed, the model system database is created with settings of 1 MB and 10% for the data and log files respectively. Obviously those can then be changed in the model database so that any user database created thereafter should pick up the revised model database settings at the time of their creation.
However, if a user database is created via a script without specifying default auto-growth settings but is not scripted via the SSMS GUI, it doesn't necessarily pick up the model database settings, but those generated at the time of SQL installation instead. I've verified this on both SQL 2012 and SQL 2008 R2 and further research indicates this is only fixed in SQL 2016.
If it is possible, can those instance installation default settings only be changed at the time of installation or can they be changed after the install? In all the SQL instance installs I've ever done, I don't remember an opportunity to do so during the process and I can't find where these settings are stored within the instance.
Please note that I'm not talking about changing the settings of the model database and, for the scenarios that have prompted this question, specifying the auto-growth settings in the script to create the user databases is not practical.
I've seen a couple of articles that sort of hint that changing the default settings within a SQL instance installation MIGHT be possible, but have not yet been able to find anything that actually says how to do this despite searching for a while. I'm hoping it's a simple answer! 🙂
Thanks in advance,
Chris Stride
August 30, 2016 at 4:57 am
Hi,
Anyone have any ideas on this?
August 30, 2016 at 6:08 am
Microsoft are aware and bug closed - "as by design" https://connect.microsoft.com/SQLServer/feedback/details/778069/default-datafile-settings-not-applied
August 30, 2016 at 6:35 am
Cheers gfoxxy93.
I had come across that article when I was researching the topic. Was very surprised it's only been fixed in SQL 2016 so that all user database creation, irrespective of method/source, will follow the model database settings.
My question is more about whether the SQL instance installation default settings of 1 MB and 10% for the data and log files respectively can be set at the time of installation or changed after the install. It might be within the SQL Server code itself and hence not accessible.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply