Determining the state of the database before or during cleanup
Before starting the cleanup process, you might want to check up on the state of your database — to discover which database tables have been filling up and how fast, check that table statistics are current, or check on the progress of cleanup tasks, past and present.
The following table summarizes several methods for checking up on the state of the database:
|Health and Value Dashboard|
Thegathers information about the database, such as when statistics were computed, tables sizes, when the last cleanup was run, and how much was recently cleaned up.
You can run various database queries to determine how much data needs to be deleted from the database, as in the following examples.
You can also use queries to check how much data is stored in key tables, broken down by month. You can create such queries for any key table that you choose, provided that the table includes a date by which you can group the data.
Expand for sample queries on key tables in an Oracle database...
Expand for sample queries on key tables in a SQL Server database...
|Database scripts for reports on tablespace growth (Oracle)|
Modify these scripts to match your environment and set them to run as sysdba.
The scripts send you an email every day, with information about how much tablespace storage was consumed by the tables, compared to the day before.
This method can also be useful to spot problematic trends (for example, someone enabled Audit Trail logging on Server.Read).
|Gather database statistics using SQL scripts|
BMC provides a SQL-stored procedure for calling the gathering of Oracle statistics that are specific to the Server Automation Database schema.
This package of scripts is located in the ..\db_scripts\oracle\utility\gather_schema_stats folder, which is created when you extract the BBSA<version>-<platform>.zip that you download from the EPD site.
To use these scripts, disable the Oracle default scripts and run the BMC-provided scripts at least once a week.
For more information, see Database performance recommendations.
For more information about setting up the SQL Server, see Setting up a SQL Server database and user for BMC Server Automation.
|Gather database statistics using a BLCLI command (Oracle)|
The dbdiagnostics utility helps you determine whether database statistics are up to date. This utility is stored in the <installationDir>/NSH/br directory.
To run the dbdiagnostics utility, use the following command:
To view dbdiagnostics results, use the following command. The following example shows the returned results, as well.
For more information, see Database Diagnostics tool.
|Query the DELETE_TASKS table|
The DELETE_TASKS table lists cleanup actions when you perform a database cleanup using the Delete cleanupDatabase BLCLI command. This occurs during online database cleanup when run in TYPICAL mode or in CLEAN_DB mode.
Click here for a sample query to the DELETE_TASKS table...
The following sample query to the DELETE_TASKS table helps you find out whether any long running cleanup task is in processing stage: