Calculate log space required on principle for maintenance activity

Question

Calculate log space required on principle for maintenance activity

ramana3327

SSCoach

Points: 19722
More actions
March 26, 2023 at 10:58 am

#4165136

Hi all,
We have a database configured for asynchronous mirroring. We have some planned maintenance activity on mirror server. Expecting for 6-8hrs. How to calculate the log space requirements on Principle server after you run the command on principle
alter database set partner suspend

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

There is no true and tested method, and it all depends how you're doing the maintenance.

If doing things in serial, like most index rebuilds etc, find your biggest index size and double it, that will be a best guess as to the amount of space needed.

If you do things in parallel, well then you need to think about how many parallel operations you can have, then find the top X indexes, add them together then double that.

But seriously index maintenance is becoming a thing of the past (rare cases exist where it still may be needed) but you should ideally just focus on statistics maintenance now. Even Microsoft say to focus on stats is all your doing is rebuilding indexes for performance.

And I wouldn't be putting the mirroring session into suspend, that will greatly impact your RTO and RPO, unless your customer is happy to lose that much data.

Also I would be looking to strip mirroring out and put in an AOAG at your earliest convenience, as mirroring is now a depreciated feature and should be replaced ASAP.

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Data always commit on Principle first with asynchronous mode.

Why data will be lost with pausing mirroring?

We don’t have any database maintenance jobs (Index/stats) scheduled during that time period.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Ah OK, your talking about OS based maintenance not SQL / DB maintenance.

So take a look at MSDB and the backup history tables, work out how big the log backups are in the same period that maintenance will be running and add a little bit extra to that calculation to be safe.

re data loss. If something was to happen to the DB in that period of time that mirroring was paused, what would be your preferred failover method? You will have lost any data that hasn't been synced, so if the primary dies and all you had was the secondary you have lost data as nothing has synced in 6-8hours, is the client happy with that if the primary was to completely stop working?

ramana3327 SSCoach Points: 19722 More actions · Answer 4

Thanks.

Is it better to suspend the mirroring when mirror server is not reachable for long period of time or leave as it is?