June 20, 2022 at 9:04 pm
I will be running the application upgrade process which will run for couple of hours and behind the seen it will make bunch table changes such as create the new table with the field changes and then insert it back into the new table etc. So since my environment is always on AG with couple of secondary replicas. I know I will have to test this process, but need some thoughts? What if I add the SQL Service account for Perform Volume maintenance task privilege and also increase the autogrowth for SQL Server database currently set for 10% and increase it to 20%. Would this help the process to run better without any issues?
June 20, 2022 at 10:21 pm
Not clear on your question - if you don't have instant initialization enabled then every data growth will take a long time. And that setting at any percentage value is a huge problem in itself. You should have autogrowth settings on both data and log files set to a MB value - and have instant initialization enable.
If you know the database is going to grow by xx MB during the upgrade, then pre-grow the files to support that extra usage.
As for the AG - how you handle that will depend on how much data is being inserted/updated and the network latency between nodes. One thing you can do is switch any synchronous replicas to asynchronous during the upgrade, that way you are not slowing things down due to a two-phase commit waiting on the secondary to commit before the primary can commit the changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2022 at 8:06 am
just to add to Jeffrey's hints:
Keep in mind your secondaries will also need the same size available !
Don't forget your LDF files ! (these do not benefit from IFI ! , so pre-size whenever you can ! )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 27, 2022 at 1:15 pm
I would agree on the mdf and ldf file growth as stated above. It should be to a reasonable MB growth not percentage. The reason is if your files gets large it is going to autogrow at a much larger chunk.
How often do you run trans log backups? You could run them more frequently during this application upgrade process is you are worried about trans log growth. Just keep in mind if there are a lot of changes it will depend on the speed of the connection between your replicas as well as it will keep the data in the trans log long enough to commit them to the replicas.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply