The servers are using database mirroring for high-availability running SQL server standard on newer hardware.
The existing plans were setup by a DBA many years ago and are using the built in maintenance tools available in the UI.
Daily: Full Backup of ALL databases Transaction log backups every 15 minutes for user databases Check database integrity of all databases including indexes Clean up history Cleanup backups
Weekly: Reorganize indexes on all databases - include tables and views and compact large objects Update statistics on all database - include tables and views, update all existing stats with Full scan
On Sunday mornings approximately 1 hour after the reorganize task the server becomes unresponsive and I have to stop the SQL service to initiate a failover to the mirror server. I assume that the reorganize or update stats is causing the issues. Wondering if I should be doing the compact routine at all and/or running the reorganize on every table (system and user).
How can I modify my tasks to reduce stress on the server but still perform the proper maintenance?