Rebuilding large production database files with minimal downtime.

[PCollins]

We need to rebuild a database whilst keeping a live copy running, the method we have used for years follow these steps:

• Take a full backup and truncate log
• Rebuild backup DB
• Truncate live log and apply to rebuilt DB
• Rinse and repeat the last step truncating the live log and applying until such point as the live db can be stopped and the last live log applied to the rebuilt database within a suitable timeframe before starting the rebuilt db in production.

Originally this could be done within working day, but as the Database is now over 500GB rebuilding takes many days, meaning multiple daily backups have happened, each with a daily log and all these have to replayed on the rebuilt db in order before catching up on current live transaction log which takes a good while as well.

Does anyone have a better method?

[VolkerBarth]

I don't have practical experience with those issues, just to clarify: Do you already use the v17 feature of dbbackup -ao/-aob? In my understanding, this is designed to simplify the "rinse an repeat" steps...

[PCollins]

Thanks, I hadn't seen those options and will take a look

[RiclWash · Accepted solution]

We have 460 GB database files. It takes about 19 hours to perform a rebuild. Not sure if it is an option or benefit for you, but we run the database in an HA (we can't have our db down for any more then a few mins 24/7/365). The rebuild is done live on the mirror of the database in the HA.

[PCollins · Accepted solution]

That's interesting use we hadn't considered, clearly the DB's don't need to be the same version in HA pairs?

[VolkerBarth · Accepted solution]

To understand:

• Is the mirror server active during the rebuild?
• What do you once the rebuild is done? Copy the database to the primary?

[RiclWash · Accepted solution]

They do not. I'm sure you don't want them to spread out.

https://help.sap.com/docs/SAP_SQL_Anywhere/e38b2f6217f24bdb90a3ff8ae57b1dd5/b5a46def58654f52acc26c9f...

[RiclWash · Accepted solution]

Once the rebuild is done, I replace the mirror database files with the rebuilt ones. I let that run as is for some time, then failover the DB to the mirror. Once that runs for some time, I copy over the rebuilt database files to the node that is now the mirror. ZERO downtime.

The mirror does show as active, but if the DB fails over to it, there would be a resource issue, and the rebuild would have to be stopped immediately. Thankfully, this has never been put to the test.

[MCMartin]

no better approach, but what helped us in speed up the rebuild was to increase the pagefile to an extreme extent (e.g. 100GB or more) and of course use a fast SSD/Nvme for storage.