Example weekly database cleanup plan

This topic walks you through an example end-to-end process of setting up a database cleanup plan. The cleanup plan described here is based on a cleanup job that runs once a week, according to the frequency recommended by BMC for standard cases (that is, for databases that contain BSA data in an amount that can be handled effectively by the online DB cleanup mechanism).

 This topic includes the following sections:

How to set and enable retention times

Before running Database Cleanup Jobs, you must set retention periods for two types of BSA data. You must also enable a retention policy, so that data that is older than the defined retention periods is marked for deletion. For more information about tasks related to retention periods and the retention policy, see Marking data for deletion.

	Step	Example screen
1	Set a retention period for job run data and job results through the Property Dictionary. In this example we define a retention period of 30 days (the length of time recommended by BMC) for all jobs of all types. In the BMC Server Automation Console, from the Configuration menu, select Property Dictionary View. In the Property Class Navigation pane, expand the Built-in Property Classes node, and select the Job property class. In the property list on the right, select the Properties tab. Select the RESULTS_RETENTION_TIME property, and then click Edit Property. Ensure that the Use this default value check box is selected, and then, in the Default value column, enter 30 as your retention period value (in days). Note  To see how job run data is displayed in the GUI, click here. Job runs are visible in any job's Results panel; each job run is accompanied by a green (success) or red (failure) icon and a date stamp.  For information about additional options for setting retention times, click here. In this example we have set one retention period for all jobs of all types. You can, optionally, set different retention periods for different types of jobs or even for specific jobs. The more specific retention periods override the more general retention periods. For more information about these options, see To set the retention period for job runs. For more information about setting properties through the Property Dictionary, see Adding or modifying properties. This retention time is not applied to the audit trail, job run events (that is, job run log messages), and job schedules. These data types accumulate faster than the other types of job data. Therefore, they have a shorter default retention time of 14 days.
2	We will be using the BMC Server Automation Application Server console (the blasadmin utility) to set a retention period for auto-generated objects and to enable the retention policy. To start the Application Server Administration console, perform the following step: Windows: From the Start menu, select Programs > BMC Software > BladeLogic Server Automation Suite > Utilities > Application Server Administration. UNIX: From the directory where BMC Server Automation is installed, enter ./br/blasadmin For alternative methods, see Starting the Application Server Administration console.
3	Auto-generated objects typically include BLpackages involved in compliance remediation or patch remediation, as well as Deploy Jobs and Batch Jobs from these remediation processes. To specify a retention period for auto-generated objects, enter the following command: set Cleanup AutoGeneratedRetentionTime 30 In this example, we are setting the retention time to 30 days, as recommended by BMC.
4	To enable the retention policy. Enter the following command: set Cleanup EnableRetentionPolicy true
5	For your changes to take effect, restart the Application Server. Perform the following steps: In the BMC Server Automation Console, select Configuration > Infrastructure Management. Expand the Application Servers node. Right-click the Application Server and select Restart. For alternative methods, see Restarting Application Servers.

How to schedule a weekly database cleanup

To perform a weekly online database cleanup, you schedule an NSH Script Job named BSA Recommended Database Cleanup Job to run once a week. This job is provided out-of-the-box. In addition to scheduling the job, you must configure several of the parameters that the job inherits from the BSA Recommended Database Cleanup Script. (For more about these out-of-the-box objects, see Default database cleanup script folders and files.)

	Step	Example screen
1	To open the job in the BMC Server Automation console, expand the Jobs folder and then navigate to BMC Maintenance > BSA Recommended Database Cleanup Job. Right-click the job and select Open.
2	To customize job parameters, in the content editor on the right click the Parameters tab, and then perform the following steps: Ensure that the ExecutionMode parameter is set to TYPICAL (in the Value column). Through the Flag runtime usage column, set the following parameters to Ignore. This will force the NSH Script Job to use default values for these parameters without requiring input. MaxDuration DurationDistribution ObjectType RoleName TargetSize Set the RetentionTime parameter (through the Value column) to 30, the same number of days that you set through the RESULTS_RETENTION_TIME property in the Property Dictionary. For more information about the script parameters, see Script parameters.
3	To schedule the job to execute once a week, perform the following steps: Click the Schedules tab. On the Schedules tab, click New Schedule. In the Scheduling box, schedule a recurring job to run once a week. It is recommended that you run this job at a time of low workload in your environment (such as the weekend). In this example, the job is scheduled to run every Saturday night. Note If you use BMC Decision Support for Server Automation to create reports, be careful that your weekly cleanup job does not overlap the weekly ETL job for loading data to the reporting data warehouse. Typically, you first run the ETL job, and only after the ETL job has finished you run the database cleanup job. It is not recommended to run cleanup while running updates of database statistics (Oracle or SQL Server), so as not to impact the performance of the environment.
4	Save the job to apply all changes.

Where to go from here

For more complicated situations, where you have accumulated large amounts of BladeLogic data in the database, whether due to failures in the basic weekly cleanups or due to the large size of your operational environment, you might need to schedule additional, more frequent jobs to clean up data (especially historical data). Such jobs use the same out-of-the-box cleanup job, but are configured to execute individual delete commands (that is, are set to run in an execution mode that is not TYPICAL). For more information and recommendations, see Example cleanup plan for large operational environments.

In more extreme cases, you might have to plan a period of downtime for your BMC Server Automation Application Server so that you can perform offline database cleanup, a more forceful cleanup mechanism that will enable you to catch up on database maintenance. Afterwards, you should again be successful in maintaining your database using the basic weekly online database cleanups.