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
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.
Select and then right-click the ModLogTrackingStatus parameter to select Properties.
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.
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.
Select and right-click on PK_ModLogTa__... found under Keys for dbo.ModLogTable. Click on modify from the dialog box.
Right-click on the gold key above to select Remove Primary Key from dialog below.
You will be prompted to confirm the deletion of the primary key.
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.
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.
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.
Reboot the Historian when complete. Enable the Historian to resume logging following the reboot. The ModLogTracking tables are now clean.