How to Delete Old History Records from GoldMine?

Version 1

    Details

    How to delete old history and mailbox records from GoldMine?

    When using SQL Express the database size is limited depending on the version of Microsoft SQL Server. This may lead to problems, especially for remote users as they often don't need all of the historical data. On the back end database the CONTHIST and the MAILBOX table are the largest tables, how can data from those tables be deleted without deleting them on the application server via the synchronization.
      


    Resolution

    - GoldMine contains a Record Deletion Wizard which also can delete old historical records which can take care of the contact history (CONTHIST records).
    - It is important to know when using
    Record Deletion Wizard for deleting old history records, that this will delete entries within the history tab until a certain cutoff date, but it will not delete any e-mails and they will remain in the database. Many times the MAILBOX table is the largest table within a GoldMine database, so in this case if old MAILBOX records should be deleted, GoldMine Technical Support can provide an officially NOT supported way.
    - The following provides some generic steps on how to use the record deletion wizard for deleting old history records and an overview about any deletions within the mailbox (which is only provided as there is no warranty) 

    ABSOLUTELY RECOMMENDED FOR BOTH POSSIBILITIES: MAKE SURE TO HAVE A FULL RUNNING BACKUP OF THE DATABASE.

    A. The Deletion Record Wizard

    1. Log in with Master rights
    2. Tools >> Data Management >> Delete Records
    3. Delete old history records
    4. If applicable, select a filter (e.g. when you want to delete only records of non customers etc)
    5. Set a specific Cutoff date (all History records before that cutoff date are deleted) + confirm that you want to delete the old history records (this is case sensitive)
    6. ABSOLUTE CRITICAL: Uncheck Synchronize Deletions as otherwise the CONTHIST record deletion will be synchronized from the remote system to the server and may lead also on the server to the deletion of those records!
    7. Finish the wizard and verify that old historical data prior the set cutoff date were deleted


    B. Back end deletion of MAILBOX records and after that CONTHIST records

    - It must be kept in mind that every e-mail record in the GUI of GoldMine consists of a MAILBOX record (with the real e-mail content) and a related CONTHIST record (for filed e-mails) or a related CAL record (for pending e-mails in inbox and sub folders from the inbox). This means that the MAILBOX and also the related CONTHIST records should be deleted as otherwise the user may get a debug message that the mailbox record is missing when double clicking on the e-mail in the history tab.

    - The following steps and especially SQL queries are only provided as is and without any warranty of any kind as we do not officially support any back end manipulation. It is highly recommended that a MS SQL DBA is performing these actions.


    ABSOLUTELY RECOMMENDED FOR BOTH POSSIBILITIES: MAKE SURE TO HAVE A FULL RUNNING BACKUP OF THE DATABASE.

    1. In SQL Server Management Studio
    2. New Query
    3. Make sure to set the database drop down to the GoldMine database
    4. Copy and paste the following query into the pane and execute this  

    Select * from conthist where srectype ='M' and linkrecid in (select recid from Mailbox) and ondate < getdate()-365

    (*) replace the getdate()-365 with the proper amount of days you want to go back from today e.g. getdate()-1825 would be about 5 years back
    >> this will provide an overview how many e-mail records will be affected by a possible delete query

    5. If you reviewed and adjusted the time frame how far back into the past you want to go you need FIRST to delete the associated MAILBOX records for those CONTHIST records (the first query is again only a select query to verify if the result makes sense while the second query is then the real deletion query)

    Select * from MAILBOX where recid in (select LINKRECID from conthist where srectype ='M' and linkrecid in (select recid from Mailbox) and ondate < getdate()-365)

    Delete from MAILBOX where recid in (select LINKRECID from conthist where srectype ='M' and linkrecid in (select recid from Mailbox)
    and ondate < getdate()-365)

    6. Now perform the Delete Old History Records Steps from A. 

    - We strongly recommend that a GoldMine user verifies the remaining data afterwards to make sure that everything worked as intended/expected, also it is absolutely recommended to document the performed actions as the MAILBOX data cannot be recovered simply by a new synchronization.