Purging out Large Batches of Old Automated Test Runs in SpiraTest

Background

The customer was looking for a test run purge tool because they had millions and millions of test runs accumulated from all their automated tests and deleting 500 at a time was not an option since it could take over half an hour to perform.

A maintenance purging tool would be the ideal solution with the same filtering as the test run main page, however for now we have this SQL query that can be scheduled by the SQL Server Agent service so that the job can be executed in the background, for example, weekly.

Solution

The following sample SQL script will purge out all test runs older than the last two (2) months, along with any history records associated with them. Note that it is written to only handle automated test runs without test steps:

-- Deletes a large number of automated test runs in small batches to avoid massive transaction log
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time to avoid taking forever
     DELETE TOP (10000)  FROM TST_TEST_RUN
     WHERE END_DATE < DATEADD(month, -2, GETUTCDATE())
     AND TEST_RUN_ID NOT IN (SELECT TEST_RUN_ID FROM TST_TEST_RUN_STEP)

  SET @Deleted_Rows = @@ROWCOUNT;
END

--Delete the History ChangeSets
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time to avoid taking forever
     DELETE TOP (10000)  FROM TST_HISTORY_CHANGESET
     WHERE CHANGE_DATE < DATEADD(month, -2, GETUTCDATE())
     AND ARTIFACT_TYPE_ID = 5
	 AND CHANGESET_ID NOT IN (SELECT CHANGESET_ID FROM TST_TEST_RUN)
  SET @Deleted_Rows = @@ROWCOUNT;
END