Repository cleanup

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.[Name] 
INTO #tempExecutionLogsToDelete
FROM [dbo].[ExecutionPackageLogs] epl 
LEFT JOIN (SELECT [ProjectId], [Name], Max([ValidTo]) as [MaxValidTo] FROM [dbo].[Projects] GROUP BY [ProjectId], [Name]) AS p
on epl.[ProjectId] = p.[ProjectId]
WHERE 
epl.[Start] BETWEEN @Start AND DATEADD(d, 1, @End)
ORDER BY [Start] DESC


DELETE FROM [dbo].[ExecutionPackageLogs] 
WHERE [ExecutionId] IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);
Delete from [dbo].[ExecutionPackageProjectVariableLogs]
WHERE [ExecutionId]  IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);
DELETE FROM [dbo].[ExecutionPackageProjectVariableLogDetails]
WHERE [ExecutionPackageLogDetailId] 
in (SELECT DISTINCT [ExecutionPackageLogDetailId] 
	FROM [dbo].[ExecutionPackageLogDetails]
	WHERE [ExecutionId]   IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete));
DELETE FROM [dbo].[ExecutionPackageLogDetails] 
WHERE [ExecutionId]  IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete); 
DELETE FROM [dbo].[LogRowCounts] 
WHERE [ExecutionId]  IN (SELECT ExecutionId FROM #tempExecutionLogsToDelete);

DROP TABLE #tempExecutionLogsToDelete