asked
Optimal AutoGrowth Settings
Hi All, I am using the following autogrowth settings on all my sql servers based upon the data the growth observed so far but the Users still complain the application is slow,am i missing something. USE [master] GO ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 1024000KB , FILEGROWTH = 512000KB ) GO ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 2048000KB , MAXSIZE = 24576000KB , FILEGROWTH = 1024000KB ) GO And i just realised that the databases are not inheriting these default growth settings that i have applied to the model database,going through the blogs found that this is a known issue and since it has a workaround this is not going to be fixed,not sure what the work around is.
https://connect.microsoft.com/SQLServer/feedback/details/778069/default-datafile-settings-not-applied Thank you, BT
did you apply those settings to model after creating the user database(s)?
Yes Thomas thats not good! i am not sure what kind of work around do i need to apply.
2 issues here: 1. You haven't got your user databases set with the autogrowth settings that you wanted - fix: you are going to have to go and amend these settings manually or carefully through scripting/Powershell 2. Users are complaining of a slow database. How is this connected to autogrowth? Have you identified that the performance is being hurt when an autogrowth event happens? If so, then fixing the settings should alleviate it.
Hi Kev, Thanks for the reply,I have applied settings manually on the model database,the weird part is that the users database are inheriting just the data file initial size property and on the replication subscribers where there arent any direct deployments the ditto settings are being inherited. I was in kind of dilemma if these autogrowth settings are causing any side effects but i am not sure. Thank you, BT
i just learnt that databases created via scripts will not inherit the settings from model :( i dont think i have an alternative rather run alter database for growth settings manually.
https://www.sqlservercentral.com/Forums/Topic1065073-391-1.aspx i am just curious if dacpac can script this changes and apply it on rest of the environment if i did on one environment?
21 People are following this question.
