Basic Database Maintenance Activites

I have been thinking about the core database server maintenance activities that are necessary for a DBA. Here are the basic database maintenance activities that I perform in my servers.

1. Setup Backup Jobs
One of my friends told me once that a DBA needs to have a good backup in place or a good resume in hand..!! Backups are the most important responsibility in a DBA's day to day activities. I usually setup a full backup (once a week or daily depending on the size of the database) and differential backups (only if the full backups are taken once a week) and transaction log backups every hour. I usually retain the backups for 2 weeks (There is also a system level backup happening in my company that is written to tape). I also try to store backups in a separate external hardware from the one where the data files are living..(Though it is not possible always..:-( ). It is also very important to have email notification setup to inform the DBA incase of any failures.

2. Setup Alerts
I usually setup alerts for any SQL Server event with severity 19 or more. This can be easily done using SQL Server Management Studio. I will write a separate post about setting up alerts. The most important thing is to setup email notification when the event happens.

3. Database Integrity Check
A corruption free database means peaceful life for a DBA. I usually check my database consistency once a week. This can also be done using a maintenance plan in SQL Server Management Studio.

4. Index Rebuild / Reorg
A fragmented Index can cause severe performance issues and could be a DBA's nightmare when he is on a hot seat to solve a performance issue. It is always good to be proactive and check the fragmentation level of all the indexes and perform rebuild or reorg as necessary. The thumb rule is to leave the index as is if the fragmentation is less than 10% ReOrg if it is less than 30% and Rebuild if it is more than 30%. Do not use the maintenance task for Index rebuild or Update Statistics if your database is huge. This will cause the job to rebuild all the Indexes in the database which could possibly run for days and would never end. Instead, it can be easily done using a script from the Following Link from books online. This script would only rebuild or reorg an Index if it is necessary.

I am sure there are other important maintenance activities as well. But, according to me, these are the core maintenance activities that any DBA should perform in his/her servers.

Hope this helps..!!