Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

adjusting the mdf file growth sizes of replicated user database

hi,

right now reviewing the database growth! and I found in terms of transactional replication, the target DB file growth size like this :


User generated image
any one can suggest if we can adjust the TARGET replicated DB growth size without breaking the replication?

I have already changed respectively the source one and no problem at all, and I expecting the same NO PROBLEM when I do it on replicated DB?

I want to change it to growth 10% each time.
Avatar of Amir Azhdari
Amir Azhdari
Flag of United States of America image

This is really up to you as a DBA. Personally I have set the db and log growths manually lets say to 100GB, and check them manually. Also added a maintenace plan to shrink the databases and their logs every evening.
I want to change it to growth 10% each time.
In my opinion that is most likely not tthe smartest thing to do as the practices/recommendations are actually to change the default 10% growth to a MB value as you have it already set even if you have Instant File Initialization turned on because your performance may suffer and potentially significantly and that depends on how good your IO subsystem handles the writes.
Related to the Publisher where you did changed it already...maybe you should review-reconsider to grow it periodically as mentioned here: https://docs.microsoft.com/en-us/previous-versions/technet-magazine/cc671165(v=msdn.10)?redirectedfrom=MSDN rather than leave it as 10% percent: 
If you're talking about Auto_Grow settings, then I would choose a number that is large enough so that you can react to it but small enough that it won't impact users and won't continue to auto_grow in multiple cycles. Ideally the amount of free space a database has will allow it to run for whatever your company standard is. For example if all databases needed to have at least 3 months of space available, then you might want manually grow the files so that it has the 3 months and set the auto_grow to some smaller number, say 3 days in case of an emergency.
More good advice/best practice can be found here as well: https://docs.microsoft.com/en-us/previous-versions/technet-magazine/cc671165(v=msdn.10)?redirectedfrom=MSDN  
Avatar of marrowyung
marrowyung

ASKER

lcohan ,

because your performance may suffer and potentially significantly and that depends on how good your IO subsystem handles the write

is a bad SAN!

tthe smartest thing to do as the practices/recommendations are actually to  

why change to MB is good ? I don't want it to keep growing ! growth rate like 1MB each time is not good!

Related to the Publisher where you did changed it already...maybe you should review-reconsider to grow it periodically as mentioned  

still manually ?

Amir  ,

Also added a maintenace plan to shrink the databases and their logs every evening.
people here suggest do not do this , just backup the log with truncate.

so all, changing the growth rate configuration or the DB in the replicated DB (subscriber size) is NOT going to kill replication once changed?

why change to MB is good ? I don't want it to keep growing ! growth rate like 1MB each time is not good!
From the screenshot you posted above the growth is not 1MB but 1000MB = 1GB and why change to MB I provided few links that explains in detail why and how. Just think that you can't apply the same growth rate to ALL servers ALL db's like bread and butter just because the obvious reasons as explained in detail at those links - simply put each grows at its own pace depending on the OLTP it takes.
simply put each grows at its own pace depending on the OLTP it takes.

I accept that and it is a good reason!@
do you suggest IFI?  good to use ?

this one : Instant File Initialization - Brent Ozar Unlimited®  is not going to help on how to set it up , any URL on how to set it up and monitor it ?

you can't apply the same growth rate to ALL servers ALL db's like bread and butter just  

usually how you monitor log or data growth sizes?
BTW, I found out using test platform and adjusting the grow setting of a replicated DB IS NOT going to kill the replication.
ASKER CERTIFIED SOLUTION
Avatar of marrowyung
marrowyung

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial