SQL Server 2005 database DBA Checklist for database administrator

This article provides SQL Server 2005 database DBA Checklist for database administrator.

The objective is to provide

• Check list for database administrator
• Overcome existing database server issues like CPU Utilization more (95%), growth in transaction log

Database Performance (CUP Utilization)

SQL Server 2005 database is 64 bit, its basic feature is it tries to use all the available physical memory that is supported by operating system.

• Set maximum server memory for SQL Server 2005 and leave rest for the system to use for other applications
• Let say your server have 8 GB RAM, You can setup maximum memory to 6 or 7 GB depending on usage and rest of the memory for other application/processes on database server.

How to set maximum server memory?

• Right Click on database server-> Click Properties

• Set Maximum Server Memory as per the need

This will avoid 95% of server utilization.

Database free space

Current Process:

In existing server free memory space is more than 50% of size. It’s recommended to have more than 20% of free memory.

• Do daily checkup of free memory space on database server as a part of daily task and do the necessary action if space is not enough like shrink the database or extend the size of the hard disk as per the requirement.
• Check on transaction log file growth and take necessary action to free the space (See 3.3 for more details).

Database Server hardware

• Recommended to check windows event log daily to verify any issues with hardware.
• Do regular De fragmentation to improve performance of the SQL server request.

DBA Check List

1) Backups

You can setup more than one backup process to make sure more options are available incase of disaster. Do regular testing of backup tapes by restoring in test environment and verifying all the transactions.

You can do backup by various ways :

• Direct backup of .mdf & ldf file (Current way of backup).
• Full backup of database by maintenance plan functionality in SQL server 2005.
• Differential backup by maintenance plan functionality in SQL server 2005.
• Transaction backup by maintenance plan functionality in SQL server 2005.

2) Review Error Log

There are several areas where SQL Server logs information about processes that are occurring as well as errors that occur. The most used is probably the SQL Server Error Log. This error log gives you startup information, integrity check information, backup information, etc… as well as any SQL Server errors that occur. In addition to this log, there is also a log for SQL Server Agent and now in SQL Server 2005 Database Mail. In addition to these internal SQL Server logs you should also use the Windows Event Log to find other errors that may be occurring or possibly additional information that is not in the SQL Server logs.

3) Setup Maintenance Plan to avoid transaction log file growth

General problem faced by most the database server is growth in transaction log file. This causes less free memory space on database server.

To recover from a situation where the transaction logs grow to an unacceptable limit, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file.

4) Other Maintenance plan

You can schedule other maintenance plans like clean up history

• Drag History Cleanup task.

• Right Click on History Cleanup Task and click on Edit option.

• Select the historical data to delete: This will allow you to delete old SQL server error log, backup history log & maintenance plan history log. Set number of days or weeks or months old data option and schedule the maintenance plan.

Related Posts

1. (DBA TIPS) Maintenance Plan to avoid Transaction log file growth
2. Error: SQL1762N Unable to connect to database
3. Backup SQL Server Database using SSMS
4. SQL Server 2000 Maintenance Jobs Failed with Error 22029
5. SQL Compare – Compare and Synchronize tool for SQL Server Database

Tags: database administrator checklist, dba checklist, SQL Server 2005, SQL Server 2005 checklist, SQL Server DBA Checklist