• Insights

One Simple Way to Keep Your iManage Databases Trim

Brian Podolsky

2 min read

All Insights

Recently I was assisting a firm with some iManage database upgrades from 8.5 to the latest 9.x schema. If you haven’t done this yet, you should know that it is a fairly intensive process since the database gets converted to support Unicode.  In the firm’s testing, they saw ballooning transaction log files well beyond what I have seen before. Some log files were nearly ten times the size of the actual database itself.  Taking a deeper look at their systems, we found that more than 80% of the actual data in their databases was held by the UserHistory table.  This is different from the Document History tracking, which the end user sees when viewing the History of a document.  The User History maintains an audit of logins and logoffs.  The options for this logging are accessed by right-clicking on your iManage library in the Database Administration program, choosing “Database Options,” and then clicking the User tab on the right-hand side of the dialog.

Options for UserHistory logging
Options for UserHistory logging

By default, WorkSite does not log Successful Logins or Unsuccessful Logins, but it does log Impersonated Logins and Impersonated Logoffs.  In theory, it’s great to log the Impersonated Logins because it is a powerful feature that could allow someone to do some damage if the Impersonation password fell into the wrong hands.  (Note: the Impersonation Password should be considered a highly-classified state secret, and should never be stored in plain-text or shared). However, there are so many WorkSite modules and add-ins that require using the Impersonation password, that if this is logged, the UserHistory table will quickly become your largest data hog.  Nearly every time any one of the following modules/add-ins does anything, rows may be added to the UserHistory table if the Impersonated logging is enabled:   WorkSite Communications Server (Pre-9.0 SP1), WorkSite Mobility Server, and matter centricity tools such as DocAuto Workspace Manager, RBRO Solutions WorkSite System Manager, Prosperoware Milan, and many others.

My recommendation is to either disable logging for the Impersonated Logins and Logoffs or to create a recurring SQL job that will trim this table, only keeping a limited number of days for the UserHistory table and offloading content to a separate database if needed for historical purposes.  One other recommendation would be to log Unsuccessful Logins.  These shouldn’t occur often, but are useful in troubleshooting access issues and providing a view into potentially malicious login attempts.

Keeping this table trim will go a long way toward keeping your overall database sizes trim and will provide the extra benefit of greatly reducing the time and disk space needed to upgrade the schema to the latest 9.x version.