SQL Server database : Regular maintenance

If you are a DBA it is very important for you to know maintenance plan in SQL Server. Generally we use SQL Enterprise Manager to perform regular maintenance of SQL database.

The information below does not cover everything you can do to maintain your SQL database in SQL Enterprise Manager. Before you must See your SQL documentation for details on what else you can do to maintain your database.

Backing up the transaction log is not compatible with simple recovery. If you have multiple databases with different recovery models, you can create separate database maintenance plans for each recovery model. In this way you can include a step to backup your transaction logs only on the databases that do not use the simple recovery mode.

Change recovery model to simple

Simple recovery mode is recommended because it prevents the transaction logs from swelling. In simple recovery, once a checkpoint is complete, the transaction logs for the time before the checkpoint are dropped from the active database. A checkpoint automatically occurs when the backup is made. We recommend having a database maintenance plan that performs a backup of the ePO database, together with "Simple Recovery". In this way, once a backup is successfully created, the portion of the transaction log in the active database will be dropped as it is no longer needed since a backup file exists.

Set the recovery model to simple. This is a one-time change to your SQL Server settings, and it is very important. While MSDE databases install with the simple recovery model by default, SQL Server installs using a different recovery model that doesn’t allow the transaction log to be cleaned as easily. This can cause the log to swell in size.

To change the SQL Server recovery model to simple:

1. In the console tree, in SQL Server Enterprise Manager under Microsoft SQL Servers | SQL Server Group | <database server> | Databases, right-click <database>, then select Properties. The Properties dialog box for the selected ePolicy Orchestrator database appears.
2. Click the Options tab.
3. Under Recovery, select Simple in Model, then click OK.

Run the Enterprise Manager Maintenance Plan Wizard , If not using simple recovery mode for SQL, you should backup the transaction log on a regular basis.

1. Open SQL Enterprise Manager.
2. In the console tree under Microsoft SQL Servers | SQL Server Group | <database server> | Management, right-click Database Maintenance, then select New Maintenance Plan.
3. When the Database Maintenance Plan Wizard appears, click Next.
4. In the Select Databases dialog box, select These databases, then select the user database and deselect the system databases: master, model, and msdb.

The name of the user database is the name of the ePolicy Orchestrator database. The default name of ePolicy Orchestrator databases is epo_<server>, where <server> is the name of the ePolicy Orchestrator server.

5. Click Next. The Update Data Optimization Information dialog box appears.
• Select Reorganize data and index pages.
• Select Change free space per page percentage to, and type 10 as the percentage.
• Select Remove unused space from database files.
• Schedule the data optimization tasks to execute during off-peak times. Click Change to change the default schedule.
6. Click Next. The Database Integrity Check dialog box appears.
7. Select Check database integrity and Perform these checks before doing backups.
8. Click Next. The Specify the Database Backup Plan dialog box appears.
9. Schedule the database backup tasks to execute during off-peak times. Click Change to change the default schedule.
10. Click Next. The Specify Backup Disk Directory dialog box appears.
11. Select Use the default backup directory.
12. Click Next. The Specify the Transaction Log Backup Plan dialog box appears.
13. Select Back up the transaction log as part of the maintenance plan and Verify the integrity of the backup when complete.
14. Schedule the transaction log backup tasks to execute during off-peak times. Click Change to change the default schedule.
15. Click Next. The Specify the Transaction Log Backup Disk Directory dialog box appears.
16. Select Use the default backup directory.
17. Click Next three times. The Completing the Database Maintenance Plan Wizard dialog box appears.
18. Click Finish.