SQL Server 2008 DBA Daily Routine Maintenance Tasks
Maintenance tasks requiring close and regular attention are commonly checked each day. DBAs who take on these tasks daily ensure system reliability, availability, performance, and security. Some of the daily routine maintenance tasks include the following:
■ Check that all required SQL Server services are running.
■ Check Daily Backup logs for success, warnings, or failures.
■ Check the Windows Event logs for errors.
■ Check the SQL Server logs for security concerns such as invalid logins.
■ Conduct full or differential backups.
■ Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.
■ Verify that SQL Server jobs did not fail.
■ Check that adequate disk space exists for all database files and transaction logs.
■ At least monitor processor, memory, or disk counters for bottlenecks.
SQL Server 2008 DBA Weekly Routine Maintenance Tasks
Maintenance procedures that require slightly less attention than daily checking are categorized in a weekly routine. The following list details these weekly tasks:
■ Conduct full or differential backups.
■ Review Maintenance Plan reports.
■ Check database integrity.
■ Shrink the database if needed.
■ Compact clustered and nonclustered tables and views by reorganizing indexes.
■ Reorganize data on the data and index pages by rebuilding indexes.
■ Update statistics on all user and system tables.
■ Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.
■ Manually grow database or transaction log files if needed. Adjust automatic growth values if needed.
■ Remove files left over from executing maintenance plans.
SQL Server 2008 DBA Monthly or Quarterly Maintenance Tasks
Some maintenance task are managed more infrequently, such as on a monthly or quarterly basis. Do not interpret these tasks as unimportant because they don’t require daily maintenance. These tasks also require maintenance to ensure the health of their environment, but on a less regular basis because they are more self-sufficient and self-sustaining. Although the following tasks may appear mundane or simple, they should not be overlooked during maintenance.
■ Conduct a restore of the backups in a test environment.
■ Archive historical data if needed.
■ Analyze collected performance statistics and compare them to baselines.
■ Review and update maintenance documentation.
■ Review and install SQL Server patches and service packs (if available).
■ Test failover if running a cluster, database mirroring, or log shipping.
■ Validate that the backup and restore process adheres to the Service Level Agreement defined.
■ Update SQL Server build guides.
■ Update SQL Server disaster recovery documentation.
■ Update maintenance plan checklists.
■ Change Administrator passwords.
■ Change SQL Server service account passwords.
From book "Microsoft SQL Server 2008 Management and Administration".
No comments:
Post a Comment