SQL Server 2008 Maintenance Practices Best Practices

Some important best practices from the chapter include the following:


■ DBAs should fully understand all maintenance activities required and implemented within the SQL Server environment.

■ Use the Maintenance Plan Wizard to automate and schedule routine maintenance operations.

■ When creating maintenance plans with the wizard, leverage the features included in SQL Server 2008 and create independent schedules for subtasks.

■ Maintenance tasks should be scripted, automated, and fully documented.

■ Maintenance tasks should be conducted during nonpeak times or after hours, such as on weekends and after midnight.

■ When you configure the order of the maintenance tasks, backups should be executed first, and then other tasks that change the database.

■ Do not include the Shrink Task when creating Maintenance Plans. Manually shrink the database if needed during nonpeak hours.

■ Maintenance tasks should be grouped into daily, weekly, and monthly schedules.

■ Schedule and conduct routine maintenance tasks on a daily, weekly, and monthly basis.

■ For a large enterprise environment running many SQL Servers, take advantage of subplans and the multiserver maintenance plan.

Note from book "Microsoft SQL Server 2008 Management and Administration".