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:

Method

Details

Health and Value Dashboard

The BSA Health and Value Dashboard gathers 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.

For more information, see Using-the-Health-and-Value-dashboards. See also the BMC BladeLogic Dashboard Documentation.

Database queries

You can run various database queries to determine how much data needs to be deleted from the database, as in the following examples.

  • To check how many depot objects the database cleanup utility will delete:

    select count (*) from depot_object where is_deleted = '1';

  • To check how many compliance results are left to be deleted, you can issue the following query, which returns a count of deleted objects by month:

    select count(*),trunc(date_created,'mon') from job_result
    where object_type_id = 5107 and is_deleted = 1 group
    by trunc(date_created,'mon') order by trunc(date_created,'mon');

  • To check how many audit results are left to be deleted, you can issue the following query, which returns a count of deleted objects by month:

    select count(*),trunc(date_created,'mon') from job_result
    where object_type_id = 11 and is_deleted = 1 group
    by trunc(date_created,'mon') order by trunc(date_created,'mon');

  • To see how many snapshot results remain to be deleted, use the following query, which returns a count of deleted objects by month:

    select count(*), trunc(date_created,'mon') from job_result
    where object_type_id = 9 and is_deleted = 1 group
    by trunc(date_created,'mon') order by trunc(date_created,'mon');

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...
select trunc(event_date,'mon'),count\(*) from job_run_event group
by trunc(event_date,'mon') order by trunc(event_date,'mon');

select trunc(log_date,'mon'),count\(*) from audit_trail group
by trunc(log_date,'mon') order by trunc(log_date,'mon');

select trunc(start_time,'mon'),count\(*) from job_result_device group
by trunc(start_time,'mon') order by trunc(start_time,'mon');

select trunc(start_time,'mon'),count\(*) from job_run group
by trunc(start_time,'mon') order by trunc(start_time,'mon');
Expand for sample queries on key tables in a SQL Server database...
select convert(varchar(4),datepart(yyyy, event_date)) + '-' +
convert(varchar(10), datepart(m, event_date)) as yyyymm, COUNT(*)
 from job_run_event
 group by convert(varchar(4),datepart(yyyy, event_date)) + '-' +
convert(varchar(10), datepart(m, event_date))

select convert(varchar(4),datepart(yyyy, log_date)) + '-' +
convert(varchar(10), datepart(m, log_date)) as yyyymm, COUNT(*)
 from audit_trail
 group by convert(varchar(4),datepart(yyyy, log_date)) + '-' +
convert(varchar(10), datepart(m, log_date))

select convert(varchar(4),datepart(yyyy, start_time)) + '-' +
convert(varchar(10), datepart(m, start_time)) as yyyymm, COUNT(*)
 from job_result_device
 group by convert(varchar(4),datepart(yyyy, start_time)) + '-' +
convert(varchar(10), datepart(m, start_time))

select convert(varchar(4),datepart(yyyy, start_time)) + '-' +
convert(varchar(10), datepart(m, start_time)) as yyyymm, COUNT(*)
 from job_run
 group by convert(varchar(4),datepart(yyyy, start_time)) + '-' +
convert(varchar(10), datepart(m, start_time))

Database scripts for reports on tablespace growth (Oracle)

To monitor database growth over time, you can use a pair of provided database scripts, Daily_Table_Report.sql.txt and Daily_Table_Report.ksh.txt.

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 TrueSight Server Automation.

Gather database statistics using a BLCLI command (Oracle)

To initiate the gathering of database statistics through the BLCLI, you can use the DBManager-blGatherSchemaStats command.

For best results, create a Network Shell Script Job that runs this command on a scheduled basis.

Dbdiagnostics command

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:

# ./dbdiagnostics runDiag diabId=1000006 

To view dbdiagnostics results, use the following command. The following example shows the returned results, as well.

#./dbdiagnostics getResLastExec diagId=1000006

diagId=1000006
execDiagId=2000040
execStartTime=2016-09-21 10:00:30.0
messageLevel=INFO
message=DBMS_STATS_CHK: DBMS_STATS on the Database ran 221 days ago,
which is NOT OK.
The Expected running of DBMS_STATS is once in 15 days.
Please run BL_GATHER_SCHEMA_STATS PROC for this schema.
messageTime=2016e-09-21 10:00:31.0

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:

set lines 132
    col current_action format a80
    col duration format a15
   select
        task_id,
       cast(
             (cast(updated_at as timestamp)-cast(started_at as timestamp))
            as interval day(0) to second(0)
        )
       as duration,
        current_action,
        to_char(deleted_rows,'9G999G999G999') as deleted_rows
   from
        delete_tasks
   where
        ended_at is null
    ;

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*