History Database 6.1.2 our production has 66 million rows on jobhistory table too large to move to history DB does anyone have script too help!!

The system was install by another person and never got history db working.  Our system is almost at a state where is not able to process.  We have a large DB server 128 gigs of ram shows 40 core 2 slots hyperthreaded.

Does anyone have script or a way to mass move the history data to the history database

  • I strongly recommend contacting support. And by the way, the database performance is based strongly on your I/O performance especially while throwing 40 cores at it.

  • I have and they are working with me the support tech suggested that post on the forum to see if someone else had same problem and a solution.

  • Hi there,

    So I've had the same type of issue in version 7.0.

    Ours had about 200 million records in total across the various tables (DialogProcess, DialogWatchOperation, DialogWatchProperty, JobHistory etc.)

    Whilst yours will be slightly different in version 6.1.2, the underlying process remains similar.

    The first issue we had was that the records in those tables weren't getting marked to be moved (ReadyForExportOrDelete = 2). We had to update those on each of the tables to be migrated otherwise the history import ignores them.

    • DialogProcess
    • DialogWatchOperation
    • JobHistory
    • DialogProcessChain

    This was because the daily maintenance task failed due to the sheer volume of rows in those tables

    We then had to update a number of stored procedures that the History import processes uses both in the History DB and the main DB. Doing so allows it to copy large chunks of those records across at a time (millions).

     Amongst a few other things, this was done by primarily by using SQL temp tables before inserting into the Raw tables. Rather than the OOTB select into.

    Then using the OOTB history import process, the copies occurred normally over a period of a few weeks until everything was migrated (ReadyForDeleteorExport = 1)

    Before going down this path I'd recommend confirming whether you really need to keep all of these records. If not, it's a much more time efficient method to just truncate those tables and move on to other more productive activities.

    If you do really need those records the above mentioned changes are a way to get it resolved. Albeit in an unsupported method from One Identity.

    I'd also recommend getting a DBA involved to help out with any stored procedure updates if you're not super familiar with SQL.

  • Hi thank you for the response,

                    Yes, we worked with audit I was able truncate all but 2 years’ worth of data that still leave me with 66 million rows in job history.  I am the application admin, but if former role was a DBA.  The DBA’s have given me owner on the database.

                    I am very interested in the method you have described we have lower environments to test in.   Please attach any information the list of stored procedures you had to modify.

                                                                                                    Sincerely Robin

  • Unfortunately version 6 and 7 will be different so a list of names won't be of use.

    Have a look at the History system process VI_SourceDatabase_Import. This runs the stored procedure that does the copy/moves via a SQL Linked Server.

    It could be called HDB_PGetRawFromSource (that was the name in version 7)

    This stored procedure then pivots off into other stored procedures both in the main database and in the History DB.

    If you're familiar with SQL then it shouldn't be much trouble to walk through how they work.