Database Management
The items I listed back in the Database Maintenance Checklist are pretty standard. All necessary to keep a server healthy. How can we improve our efficiency and take the DBA role to the next level however?
Automate test restores
Restoring data can be scheduled by script and SQL Agent Jobs easily. Providing capacity is not an issue restores can be scheduled to a test environment out of core hours.
Configure Alerts
SQL can send emails on job failures and a wide range of configurable events warning of systems events. In addition to this I personally like to configure emails for Blocking Queries, Long Running queries and Server Restarts/Failovers.
Monitor Trends
Monitor the daily growth of backups, databases, data files or even tables. This can be done into a tools database and stored on server with a relatively small overhead.
Leaving this running will provide you with accurate figures for capacity planning.
Indexes
Periodically review database indexes. Identifying and removing duplicated and unused indexes will save space and reduce the overhead of maintaining them. Similarly running scripts or the Database Tuning Advisor may help identify new indexes that are needed.
Index maintenance jobs may hide an inappropriate index e.g one that quickly fragments. Recording the index fragmentation level before running index maintenance can help identify this. Another use for my tools database, a collection of scripts I have on each machine.
No comments:
Post a Comment