Stagger start time for SQL Snapshot Creation - so they don't start at same time

[uffe-bak]

Issue/Error: All SQL Snapshots are set to start at same time. Half will fail due to lack of memory or timeouts.

Request/Solution: Stagger the start time of Agent creating Snapshot, according to DB size of PREVIOUS snapshot!

Scenario:

Our company has recently updated to 2022, and everything is running smoothly with new replication model.
Except the weekly snapshot generation fails or produces invalid results for half of all Vaults!

Errors are:

“Not enough memory resources are available to process this command.”

“Exception of type 'System.OutOfMemoryException' was thrown”

"The initial snapshot for publication ' ADMS_AI2022...' is not yet available" - after a time out due to long processing time.

Turns out Snapshot agent consumes 50 MB memory for small DB and up to 350 MB for larger. When user enables replication for a Vault or CC Library, Vault creates an Agent Job for creation of new Snapshot on a weekly basis.

They are all set to start at same time (Sunday 12 AM), which leads to memory overload. In our case with 25 Vaults and 33 Libraries – that leads to memory issue, even with expanded size of Page file.

You could (and we do already) limit the memory consumption for SQL Server, but it seems counterproductive to limit the SQL performance for the whole work week, due to a job happening only once a week for maybe 2 hours. 

Suggestion/request:

When user Enables Replication of a Vault or CC Library, ADMS Console checks the size, and staggers the start time so it does not overlap with the previous job. If many changes are done, an additional “Reset Snapshot timing” for ALL jobs could also be welcomed.

Feel free to contact us if more info is needed

Regards
Uffe Bak

[Richard.Rankin]

Hi @uffe-bak 

I hope that you are well!

I stumbled across this request looking for something else and saw your name! 🙂

When replication is first enabled for a database, the Snapshot agent is scheduled for 00:00 on Sunday morning as you know. This is just a default setting that Autodesk chose because it is the time least likely to impact users. This works great if you are only replicating a few databases and memory is not a problem.

However, it is very possible to manually change that setting by editing the Snapshot job in the Publishers SQL Server Snapshot Agent. In fact, most DBAs would recognise that problem and happily change the scheduling. So feel free to edit that Agent to a time that works better for your environment. 

I'm thinking that when the PMs review this wish, they might even consider my suggestion is the solution to your and close this off.

Take care my friend! (albeit 9 months too late 🙂 )

[uffe-bak]

Hi @Richard.Rankin 
All well here with Summer vacation only a few days away - and I hope all well too for you and your family!
Funny you fell over this suggestion right now, because I were just about to send an update and actually open a ticket which could hit your Queue.

I agree that it "usually" is a minor annoyance if you have a few Vaults/CC Libraries replicated and can be handled with manual intervention.

But in our case with 25 Vaults and 35 CC Libraries - it becomes a major issue as many jobs fails, and you HAVE to make the manual changes to schedule, but can be somehow accepted if you only have to do it once.

The worst part though is (and here comes the update and why I will raise a ticket) - that Vault Server Update seems to reset any modification back to Sunday 00:00, and you know me well enough to know I do NOT handle that sort of event in a mature and levelheaded fashion!  😉
It seems like Update from 2022.1.2 to 2022.3, resets the schedule, as we have not updated SQL Server (2019 CU11).
So my Request stands - and an Update should definitely NOT reset manual changes.
Agree?
Give my regards to the Team - and wish you all a good summer.
Uffe

[uffe-bak]

Update January 2nd.
Issue reoccurred again, this time without any Vault or SQL Updates.
Logged as Ticket 19906946 and tagging @ihayesjr - to share the pain!
Regards Uffe Bak
================== Text from Ticket ====================
 Hi
This ticket is a repeat of tickets 19264380 and 19677936.
It is still the same issue - Schedules for Weekly SQL Snapshot creation are reset without our intervention. It started as a complaint after Update to Vault 2022, then a complaint that the minor update to 2022.3.2 did the same, and this time around the Schedule is reset WITHOUT ANY update to Vault or SQL Server 2019.
As shown in attached screenshot - most Snapshot jobs fail after retrying for app. 22 hours, due to the fact that all 27 Vault and 36 Content Centers' are attempted to get a new snapshot starting simultaneously at default 00:00h.
We have previously reset all the Jobs to stagger the start time with 5 - 15 mins between, but they have been reset between December 1st and now. We can see that 2 Windows Server 2019 Updates have been installed, but no updates for Vault or SQL server have been installed in this period.
The only changes we can recall have happened, are adding a new Vault and CC Library, plus adding replication of them to 2 other sites
We will now have to spend 1 hour redoing the staggered start time AGAIN, and it is getting increasingly annoying that this issue reoccurs and there is no solution.
We don't expect it to be solved here and now, but please bring our frustration to the team deciding on fixes for next update!
Regards Uffe Bak