Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 322

Transaction log backup—every 30 minutes
Weekly database optimization tasks—defragmentation and removal of
backup history
Daily database optimization tasks—update statistics

Checking database integrity—weekly

F Ensure to enable Agent XPs that in turn enables the SQL Server Extended stored
procedure on the server using the following code:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

F The SQL Agent service account must have read permission on Active Directory users.
F The SQL Agent service account must be a member of the local group Performance
Monitor User.

Each of these tasks will have a separate plan and schedule to ensure that the database
maintenance tasks are managed effectively. The maintenance plan wizard and features are
available in all the editions of SQL Server 2008 R2 except Express editions (Express Edition,
Express with Tools, or Express with Advanced Services).

For this recipe, we will create one maintenance plan with separate schedules for each to
implement the maintenance tasks.

How to do it...

The following steps are to be followed in designing maintenance tasks on a mission-critical
environment. All the steps are defined using the wizard:

1. Launch the Maintenance Plan wizard by expanding the Management node in SSMS

and right-click on Maintenance Plans to open the Maintenance Plan Wizard.

2. The wizard will have sequential dialog boxes with an explanation, like every other

Microsoft wizard. The initial screen presents an introduction on steps. Click Next
to continue.