Buildmaster database and SQL Express

Our Buildmaster database is over 6GBytes in size and Buildmaster can no longer perform deployments as it always gets a timeout. The SQLExpress process consumes an entire CPU as soon as you start a deployment.

We have restarted the Buildmaster and SQLExpress services and rebooted the machine but no improvement.

I suspect the problem is to do with the database size - much of the data is for deployments we do not want to keep history for.

How can we deleted the history from the database ?

Product: BuildMaster
Version: 4.2.5

With that level of use, we strongly recommend upgrading to a full SQL Server license as the SQL Express edition is limited to a 10GB database (4GB for pre-2008-R2 installs), 1 CPU and 1 GB RAM.

However, to free up some space with a SQL query, the general strategy is to purge execution logs for executions of rejected builds. Most builds are rejected, so this frees most of the log space that is used. There’s more places to purge as well (old audit logs, etc) if necessary.

Here’s a very basic script that will purge ALL rejected builds...

###BuildMaster 4.2 and earlier:

SELECT TOP 100 * FROM BuildExecution_PlanActionLogEntries
--DELETE BuildExecution_PlanActionLogEntries
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 and later:

SELECT TOP 100 * FROM BuildExecution_ActionGroupActionLogEntries
--DELETE BuildExecution_ActionGroupActionLogEntries
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')

But it may be good to keep the past few releases; this can be done with AND B.Release_Number IN (…) --- just add the release numbers --- or by making sure the Rejected Date > 30 days or something.

And of course as always, it's recommended to perform a database backup before running a query like this.

Thanks Tod, I have run your query and it deleted most of the rows out of the log table. However, Buildmaster is still timing out due to slow database performance.

Are there any other tables we can reduce in size.

Also we are looking to upgrade to SQL Server as soon as possible.