Deleting old database records

[Guest]

As a short term fix for the database size limitations in SQLExpress, we need to purge old records from the database.

We have already purged the log records and BuildOutputs, BuildTestResults, EventOccurenceDetails and EventOccurences.

However the BuildExecution_PlanActionVariableValues table is using approx 3.3GB of space and we do not need to keep this data for builds older than say 3 weeks.

Can you please provide some SQL which will delete old records from this table and any other linked tables.

Product: BuildMaster
Version: 4.2.6

[Guest]

NOTE : BuildMaster has Retention Policies which are strongly recommended, rather than manually purging data

That being said, you can use the same strategy to delete from that table that you used for log entries:

BuildMaster 4.2 and earlier:

SELECT TOP 100 * FROM BuildExecution_PlanActionVariableValues
--DELETE BuildExecution_PlanActionVariableValues
WHERE BuildExecution_PlanAction_Id IN
   (SELECT BuildExecution_PlanAction_Id 
      FROM BuildExecution_PlanActions BEPA
              INNER JOIN BuildExecutions BE
                    ON BE.Execution_Id = BEPA.Execution_Id
                 INNER JOIN Builds B
                    ON BE.Application_Id = B.Application_Id
                     AND BE.Release_Number = B.Release_Number
                     AND BE.Build_Number = B.Build_Number
                 WHERE B.BuildStatus_Name = 'Rejected')

BuildMaster 4.3 through 4.9:

SELECT TOP 100 * FROM BuildExecution_ActionGroupActionVariableValues
--DELETE BuildExecution_ActionGroupActionVariableValues
WHERE BuildExecution_ActionGroupAction_Id IN
   (SELECT BuildExecution_ActionGroupAction_Id 
      FROM BuildExecution_ActionGroupActions BEAGA
             INNER JOIN BuildExecution_DeploymentPlans BDP
                    ON BEAGA.BuildExecution_DeploymentPlan_Id = BDP.BuildExecution_DeploymentPlan_Id
              INNER JOIN BuildExecutions BE
                    ON BE.Execution_Id = BDP.Execution_Id
                 INNER JOIN Builds B
                    ON BE.Application_Id = B.Application_Id
                     AND BE.Release_Number = B.Release_Number
                     AND BE.Build_Number = B.Build_Number
                 WHERE B.BuildStatus_Name = 'Rejected')