I manage an OLTP database that needs an index maintenance plan designed. The requirements are that it be automated as much as possible, have no impact on batch processing by the ERP application and run at least bi-weekly.
The biggest challenge I am faced with is the amount of logging generated by index maintenance - in testing, it can fill up the drive containing the transaction logs which are being backed up every 30 minutes.
The database is using the full recovery model. From the research I have done, these are the options I have come up with:
1) Increase the size of the log partition
2) Backup logs more frequently (~15 minutes)
3) Use a different recovery model on the DB during maintenance
I am looking for helpful tips or experience to point me in the right direction, especially directions that I haven't considered.
I set up a process that tracks when a table was last reindexed and how long it took to do it.
I can then allocate a window of reindexing time and my code will reindex as many tables as it can during that time, starting with those that require the most time.
If it's not practically useful, then it's practically useless.