Skip to main content
InSource Solutions

TN Hist258 Cleaning up the Historian ModLogTracking Tables

  1. Last updated
  2. Save as PDF

InSource_Logo_Transparent.png

Description

 

This article from InSource shows how to cleanup or truncate the two ModLogTracking tables found in the Historian Runtime database.

  • Author: Rich Brooks
  • Published: 7/18/2016
  • Applies to: Historian 2014R2 and higher

Details

Modification Tracking is an optional feature provided by the Historian.  The feature (when enabled) tracks inserts, updates, and deletions to or from columns in the runtime database.  There is the potential to log hundreds of millions of records over time when this option is enabled.  It is sometimes necessary to cleanup or truncate the tables to prevent them from growing too large.  

The ModLogTrackingStatus parameter is enabled from the ArchestrA System Management Console | Historian | Historian Group | <server name> | Configuration Editor | System Configuration | Parameters.

Capture1.PNG

Select and then right-click the ModLogTrackingStatus parameter to select Properties.

Capture2.PNG

The options for logging are listed below:

0 = none (default)

1 =  inserts
2 = updates
3 =  inserts + updates
4 = deletions
5 = inserts + deletions
6 = updates + deletions
7 = inserts + updates + deletions

Set Value to zero to turn off the modification log tracking option.

Note:  It is critical to have a good backup of the Runtime database before proceeding.  Create a backup the runtime database from SQL Server Management Studio.  Please refer to the SQL Server help for instructions on backing up the database, if needed.  

Open the System Management Console to select ArchestrA System Management Console | Historian | Historian Group | <server name> | Management Console | Status.

Capture3.PNG

Right-click on Status to select All Tasks | Shutdown (and disable ) Historian... from the drop down list.  It is safest to shutdown the Historian during cleanup, but not required.

The next step is to open the SQL Server Management Studio to edit the ModLogColumn and ModLogTable tables.  These tables have a primary key and constraints that prevent the truncate of the tables.  Expand the Object Explorer in Management Studio to locate <server name> | Databases | Runtime | Tables | dbo.ModLogTable | Keys.

Capture4.PNG

Select and right-click on PK_ModLogTa__... found under Keys for dbo.ModLogTable.  Click on modify from the dialog box. 

Capture5.PNG
 Right-click on the gold key above to select Remove Primary Key from dialog below.

Capture6.PNG

You will be prompted to confirm the deletion of the primary key.

Capture7.PNG

Click on the Yes button to confirm.

Next select and right-click on FK_ModLogTable_HistorianSysObjects found under Keys for dbo.ModLogTable.  Click on modify from the dialog box.  

Capture8.PNG
Change the Enforce Foreign Key Constraint from Yes to No and click on close.

You may then run the following query to truncate the two associated tables.

USE Runtime

TRUNCATE TABLE ModLogTable

TRUNCATE TABLE ModLogColumn 

Running a Repair from the installation DVD will restore the primary key and constraints that were removed from the database in the previous steps.

Capture9.PNG

Reboot the Historian when complete.  Enable the Historian to resume logging following the reboot.  The ModLogTracking tables are now clean.