We have a new community! Please visit support.timextender.com
Follow

Why is my project repository database so large, and how to clean up?

Author: Doug Wynkoop Last updated:
tx2012 TX DWA tX2012 Project Repository

This article has been merged with another article "Size issues with your repository - Clean up the logs and get more space" originally written by Thomas Lind.

A variety of factors can contribute to the growth of your TimeXtender repository database.  Depending on the cause, there are a number of remedies that you can apply.

Execution Package Logs

TimeXtender records details of every execution.  These logs can begin to take up a lot of disk space, especially if your projects have execution packages that run more than once per day.  Removing older logs can help reduce the repository's size, although you will not be able to restore those later.

To remove these logs, open the Tools menu, select Repository Administration, and click on the Execution Package Logs tab:

mceclip1.png

From here, you can view the logs for all of the projects in your repository.  By picking a start date, a stop date, and a project, you can control which execution packages appear in the list.  You can then select and delete older logs that are no longer useful.

To select a section of rows in the window, start by highlighting a row, then go to the end of the section and mark while pressing Shift + Left click.  You may also select all rows with Ctrl + A key.

SSIS Logging

Very large repository size and rapid repository growth is often due to SSIS logging.  To see if SSIS logging is enabled, edit your project and look to see if the Enable SSIS logging box is checked:

mceclip2.png

SSIS logging creates extremely detailed logs of the SSIS transfer process which can be useful in some advanced troubleshooting situations.  However, we strongly recommend that you disable this option unless there is a pressing need to turn it on.  The reason for this is that executing a single table only once can add hundreds or thousands of rows in the project repository database.  

If this option is turned on in your project, SSIS logs probably constitute a majority of your project repository's memory footprint on your hard disk, and we recommend turning it off.  After you uncheck this option, you will need to re-deploy any table in your project that uses SSIS for data transfer in order to fully commit the change.

SSIS log records are stored in the table "sysssislog" in your project repository database.  This is a database system table that will not show up in your project or in the repository database's list of regular tables when viewed in SQL Server Management Studio.  Unless you have a specific reason to keep these logs, it should be safe to remove them as they are not used in the TimeXtender.

In order to remove all logs in this table, open up SSMS and run the following query against your project repository. 

TRUNCATE TABLE dbo.sysssislog

a script to remove all rows not containing error messages:

delete from dbo.sysssislog where event <> 'OnError'

Repository Age

The project repository contains a record of every version of every project you have ever created.  Over the years, this can eventually cause the repository database to grow.  However, it's not always necessary to keep very old versions of the project easily accessible and archiving these records can save disk space.

To archive your old repository, export all of your current projects and make a backup of your repository database.  Then, drop your repository database and re-create a new version of the repository through the TimeXtender UI.  Finally, import your projects into the new repository.

Note that if you are using multiple environments, you will need to reconfigure your environment settings in your new repository.

While the new repository takes up much less space on the disk, and certain functions may run faster, accessing older versions of your project will become more challenging after archiving the old repository.  To restore to an earlier version, you would need to restore the archived database under a new name, open the version you want, export the project, and import it into your new repository.  However, if restoring older versions is usually relatively uncommon, this trade off may be worth considering.

Was this article helpful?
0 out of 0 found this helpful

11 Comments

Date Votes
  • 2
    Avatar
    Stuart Cuthbertson

    Could it be made possible to configure TX to automatically delete logs older than N days/weeks/months? That would be preferable to having to manually open the Repository Admin tool and delete via lots of clicking and waiting. 

    I have also commented this on a similar article:

    https://support.timextender.com/hc/en-us/articles/115004530803?page=1#comment_360000981592

    Even a SQL Agent Job that performs the required steps via T-SQL and needs scheduling via SQL Server Management Studio would be more than adequate for my needs. I could write all the required T-SQL script and create the job myself if needed, but I wouldn't want to do that without knowing for sure which tables to target, and that TX support such an action. 

  • 1
    Avatar
    Stuart Cuthbertson

    Additionally, for the benefit of other readers: be aware that the deletion of old logs is itself agonizingly slow. I found that deleting 6 months of logs, in my environment, took something like 5-10 minutes, during which the Discovery Hub client was non-responsive (looked like it might have crashed). It does however get through it eventually - give it enough time, go make coffee or something :) 

    It appears that TimeXtender is processing deletion with an RBAR pattern, so deletion time will scale linearly with the number of log events to be deleted. If any TX devs read this - you might want to see if it's possible to implement a more batch-oriented approach to deletion.

  • 0
    Avatar
    Gert Knoops

    I agree with Stuart. It is painful to click every log manually. Could you at least add a "select all" feature?

  • 0
    Avatar
    Stuart Cuthbertson

    Gert - you can use Shift + click to select all logs fairly easily. Click on the first log, then scroll down, then press Shift and then click on the last log. This should select them all. Possibly one checkbox will remain unchecked, which you should be able to add to the selection if you keep Shift held down. Hope that helps! 

    The problem I was describing is that having selected all and asked TX to delete them, it runs very, very, slowly. The more logs I select, the slower it goes. So I don't think it's taking advantage of a single batch DELETE FROM table WHERE ... query, which is usually pretty performant even with a large set of rows. I suspect it's issuing DELETE FROM table WHERE uniqueId = 1234; DELETE FROM table WHERE uniqueId = 1235; and so on. 

  • 0
    Avatar
    Gert Knoops

    Great! Thanks Stuart

  • 0
    Avatar
    CalmCo Support

    Another reason for a large projectRepository is the presence of so-called "leftover synchronization records" which basically are schemas from deleted projects that can cause a repo to bloat.

    As such for future versions it would be great to:

    a) have some supported cleaning mechanisms. Currently we run scripts manually every time against the projectRepository database and it does not make us feel comfortable.

    b) store the logging in a separate database

    The work-around of exporting every project XML is also very cumbersome when you have many projects in your repository.

    Edited by CalmCo Support
  • 0
    Avatar
    David Zebrowitz

    Importing the exported XML file will also result in new Extended Property GUIDs for database objects won't it?  This would require dropping and re-creating all tables in the target databases.  I know that doing a Save-As would have this effect.  Maybe import doesn't do that.

    Edited by David Zebrowitz
  • 0
    Avatar
    Joseph Treadwell

    Hi David, Correct. Save As.. creates all new GUIDs. However, I believe an Export/Import should retain all original GUIDS. 

  • 1
    Avatar
    Niclas Löfblad

    A little late to the party but you can select all logs by pressing CTRL+A and then pressing space bar to check all boxes. This way you won't need to scroll to the end. 

  • 0
    Avatar
    Peter Jensen

    We're experiencing projects to open very slowly using TX 20.10.38 (we're above version 10000 of the project) 
    Is there an easy way to remove old versions of a project ? We are using multi environment with global databases, and don't want to go through the export, create new repo DB and import hustle.
    We're also trying to delete the logs, but deleting them from within TX seems to take forever.

  • 1
    Avatar
    Thomas Duun

    I am using this little script to delete old Execution logs. I have setup a agent job that executes the script daily to delete execution logs older than 3 months. 

    Make sure to test if the repository structure has changed after an upgrade since the script relies on the repository database structure matching the script (but if you are on legacy 20.10.x my guess is that it will not change)

    Script:
     
    DECLARE
      @BeforeDate DateTime 
     
    SET @BeforeDate=DateAdd(m,-3,getdate())
     
    DELETE FROM [dbo].[ExecutionPackageProjectVariableLogDetails]
    WHERE [ExecutionPackageLogDetailId] 
    in (SELECT DISTINCT [ExecutionPackageLogDetailId] 
    FROM [dbo].[ExecutionPackageLogDetails]
    WHERE [ExecutionId] IN (SELECT ExecutionID from ExecutionPackageLogs where [Start]<=@BeforeDate));
     
    DELETE FROM [dbo].[LogRowCounts] 
    WHERE [ExecutionId] IN (SELECT ExecutionID from ExecutionPackageLogs where [Start]<=@BeforeDate);
     
    DELETE FROM [dbo].[ExecutionPackageProjectVariableLogs]
    WHERE [ExecutionId] IN (SELECT ExecutionID from ExecutionPackageLogs where [Start]<=@BeforeDate);
     
    DELETE FROM [dbo].[ExecutionPackageLogDetails] 
    WHERE [ExecutionId] IN (SELECT ExecutionID from ExecutionPackageLogs where [Start]<=@BeforeDate);
     
    DELETE FROM [dbo].[ExecutionPackageLogs] where [Start]<=@BeforeDate
     
     
Please sign in to leave a comment.