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:

MethodDetails
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 BMC Server Automation.

Gather database statistics using a BLCLI command (Oracle)
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
    ;
Was this page helpful? Yes No Submitting... Thank you

Comments