1

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?

Improve this question
3
  • If that was set up many years ago, check whether the hardware is good enough. Seriously. Companies grow - sometimes you just need to throw some hardware at it. Alternatively you could stretch out the sunday window. Maybe reorganize takes more than 1 hour now?
    – TomTom
    Sep 10, 2014 at 15:15
  • @TomTom - It is running on newer hardware than when the plans were setup, but the number of databases has grown and some databases have millions of records.
    – Developr
    Sep 11, 2014 at 15:19
  • Time to consider splitting then.
    – TomTom
    Sep 11, 2014 at 15:26

1 Answer 1

Reset to default
2

You don't mention what you're using for maintenance, however...

It's usually not necessary to reorganize indexes on all databases. Some of them will need it and some won't. You could probably switch to something like Ola Hallengren's scripts, which will check and see if the indexes need it first. I'd recommend something like that.

You might also want to consider what (if anything) changed in the environment. Do you have more databases than you did years ago? Did something happen to make the reindex job more painful, or was it always like this and you only noticed when you took over?

Improve this answer
1
  • 1
    The databases are setup on AWS ec2 instances (i2.2xlarge) whereas they were previously running on dedicated hardware. I will see about only running the reorganization on certain databases. Since the plans were setup a long time ago, I assume there are now better practices to consider, but not being a DBA, it is easy to get lost in the sea of information.
    – Developr
    Sep 11, 2014 at 15:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .