Thursday, April 14, 2011

Database Maintenance 2

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: