Reducing the size of a HEAT database

Version 1

    Details

    What tables are safe to truncate to reduce the size of the HEAT database? 


    Resolution

     

    Reducing the size of your HEAT Database

     

    There are 4 tables that are currently storing data that may not need to be kept.

     

    1)    HEATActv. 

    This is the activity log data for all call records that can be seen in Call Logging>>View>>Activity Log.    If this data is not used for reporting or otherwise, it can be removed through HEAT Administrator>>Defaults>>Activity Log>>Group Deletes. This will give you the option to delete records by date, status, or CallID range.  To remove all records, run the following SQL statement from the database level.

    TRUNCATE TABLE HEATActv

     

    2)    HEATNote, HEATMsg and HEATTran. 

    These tables hold the message recipient and text of mail messages sent from HEAT SupportMail and by BPAM as well as logs for transferred calls. Use the following scripts in SQL to remove the data from these tables without removing pending HEAT Alarms:

     

    a) DELETE FROM HEATNote WHERE MMsgID NOT IN (SELECT MMsgID FROM HEATMsg WHERE MType = ‘A’)

                b) DELETE FROM HEATMsg WHERE MType <> ‘A’

     

    3)       Options 1 and 2 are very quick and easy but you MAY want to consider archiving old Call Ticket data that  will never be used for reporting or for any other reason.  Archiving is a process to store your older call records in a separate database and data source, then remove them from the production database. It is not necessary, but archiving can be beneficial to the HEAT response times for Call Logging along with reduced Edit Set commit times.

     

     

     

    Before ever running delete statements, always be sure you have a current backup of the database.