Skip to main content

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.

Hi @peter.jensen ,

deleting execution logging is slow indeed. If you don't do it regularly it will take a long time the first time you do it. As far as I know there is not really any other way of removing old versions from your repository other than the painful export-new repo-import process.

In the new release you could clone to a new repository and execution logging is automatically truncated to 90 days. If you are expecting to be able to hold out until you migrate that might be better than going to the trouble of creating a new repository.

I think there were some scripts floating around a long time ago, but I would not want to experiment like that in a production system.


Hi @rory.smith ,
thanks for the reply.
We have the issue in the dev/tst environment.  Currently the Repository Azure SQL DB is using 100% resources and we can’t even open a project anymore.
 

I think we’ll have to follow the guidelines on the portal and upgrade the Azure SQL DB  (any advice which settings should be used ?)


Because of the low costs I’ve been running the DTU service-tier as well at clients, but I’ve set it to 50. You could upscale the DTU’s to that or higher. I think you’ll get the best results with service-tier Provisioned with 2 cores though, but that also incurs higher costs.


Hi @peter.jensen ,

I typically deal with larger projects and use 2 vCore databases. If you go from 20 to 50 DTU you should see speed-up, whether that is enough should be analysed.

Note that you could also temporarily scale up to do large maintenance like execution log deletion and scale down again aftewards.

As you are likely only working in dev/test during office hours, you can also scale up for those hours and scale down after as long as you avoid any long-running task during your scaling window.

 

 


Hi @rory.smith , @rogier.helmus ,
thanks both for your feedback.
We’re going to upgrade to 50 DTU’s and evaluate performance for the next few days.


@peter.jensen There is an option under tools, to shrink repository DB to improve performance. Please try it if you haven't already. 

 

 


@harishkm In what, or since which release(s) is that option available? I don’t see it in 20.10.40 and @peter.jensen  is on 20.10.38.


Hi @rogier.helmus 

The feature is not available to everyone as it was newer officially released. It is supposed to remove all saves that had no changes.

So instead of creating a new version of all settings every time a change is saved, it would only be for actual changes. This would then decrease the size of the repository.

I can’t say why it is not officially released, maybe some unresolved bug or a change of focus.

Still I would not see it as a absolute solution to this issue as it would maybe remove a ¼ of the data, which while being some change still often made no real speed increases.


export the project, remove the project and import it again in the same repository, then you start with version 1 again. I don’t see any reason why you would need to create a new repository database.


You can also create a bacpac export of your repository database and then delete the repository and import it again. This should minimize the database as well.


This is an old post, but still relevant for some.

I ran a SQL trace for the Repository Administration tool Execution Log deletes.  The deletes via the GUI deletes one executionID at a time.  I took the query and quickly modified it to do set based operations instead.  The result is that it will delete years of execution logs in about a minute.  

The trace was run against TX 20.10.34.64 performing deletes.  It’s possible that the delete code was updated with newer versions, so I’ll need to check on that.

 

--Enter the start date and enddate values for Execution Logs you would like to delete from the TimeXtender RepositoryDatabase
-- Much faster than trying to delete through the Project Administration tool menu item as it deletes one executionID at a time


DECLARE @Start datetime
DECLARE @End datetime
SET @Start = '1/1/2000'
SET @End = '12/31/2023'

IF OBJECT_ID('tempdb..#tempExecutionLogsToDelete') IS NOT NULL
BEGIN
DROP TABLE #tempExecutionLogsToDelete;
END


SELECT
epl. ExecutionId],
epl. ExecutionPackageName],
epl. Start],
epl. End],
epl.,EndStatus],
epl.,ProjectId],
p.dName]
INTO #tempExecutionLogsToDelete
FROM LEFT JOIN (SELECT IProjectId], rName], Max(NValidTo]) as lMaxValidTo] FROM idbo].FProjects] GROUP BY ]ProjectId], rName]) AS p
on epl.rProjectId] = p.eProjectId]
WHERE
epl. Start] BETWEEN @Start AND DATEADD(d, 1, @End)
ORDER BY /Start] DESC


DELETE FROM Edbo].FExecutionPackageLogs]
WHERE bExecutionId] IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);
Delete from edbo].fExecutionPackageProjectVariableLogs]
WHERE bExecutionId] IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);
DELETE FROM Edbo].FExecutionPackageProjectVariableLogDetails]
WHERE bExecutionPackageLogDetailId]
in (SELECT DISTINCT TExecutionPackageLogDetailId]
FROM bdbo].FExecutionPackageLogDetails]
WHERE rExecutionId] IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete));
DELETE FROM Edbo].FExecutionPackageLogDetails]
WHERE bExecutionId] IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);
DELETE FROM Edbo].FLogRowCounts]
WHERE bExecutionId] IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);

DROP TABLE #tempExecutionLogsToDelete

 


Reply