As an accidental DBA, I'm having trouble determining how to handle maintenance on our Orion DB. When using the built in DB maintenance plan, the maintenance jobs inevitably end up running longer and longer due to HUGE amounts of data being dumped in a few tables daily. Eventually, the jobs run for days on end. The issue is several tables are growing to tens of millions of rows with one growing to hundreds of millions before the configured 7 day retention pruning occurs. Without shortening the retention period to clean out the daily tables quicker or turning down the volume on collected node info, I'm weighing my options for running maintenance to re-org/rebuild indexes and update statistics without having locks on these tables for days.
Here's my thoughts - open for comment:
1. Disable the built-in DB maintenance (how...rename the .exe?)
2. Create stored procedures for each of the following (based on Ola Hallengren's SQL Maintenance Solution at https://ola.hallengren.com/):
a. Database backups nightly
b. Data integrity checks nightly
c. Nightly index re-org/rebuilds and update statistics (actions determined by % fragmentation)
It's my understanding that stored procedures run more efficiently than SQL Maintenance Plans or maintenance jobs built by SQL queries (which is how I believe the .exe is doing it). So, how do those of you with large environments and tons of data being stored in your DB's handle this?
Thanks for any help!