Self Cleaning Load Test Database

I ran into an issue where I was running out of storage on the Load Test Repository DB. I knew that there were a ton of old aborted runs and other runs I didn’t need. It would have taken hours to sort through them all and delete just the aborted runs because of the number of load test names that were in the results viewer. The time it takes to remove a single test was almost 5 minutes.  

Here is a Stored Procedure that you can add to the Load Test Database and execute to clean up all aborted runs. Also, if an aborted run contains some description specified by the filters it will keep it. In my case I wanted to keep aborted Runs with the term TOR in the description or the word Save.  This is going to be case sensitive!

First Add the SP and then create a job that will run on a regular interval to keep the DB clean. In order to automate your work to save some time!

You can use a similar query to the one below to see what will be deleted. These are the same filters that are used in the sample exec and will represent the runs that will be deleted.

-----------Sample Query to see what will be deleted and tune your filters------------

 select LoadTestRunID from dbo.LoadTestRun

where outcome = 'aborted'

and (Description not like '%TOR%' or Description not like '%Save%');

-----------End Sample -------------

Create this stored procedure to perform the cleanup.

 *************Start of SP code**********

 /*

Sample Exec:

 

exec dbo.Prc_DeleteLoadTestRun_Wrapper

       @Outcome = 'Aborted',

       @Desc_NotLike1 = '%TOR%',

       @Desc_NotLike2 = '%Save%'

 

*/

CREATE PROCEDURE dbo.Prc_DeleteLoadTestRun_Wrapper

(

@Outcome varchar(100),

@Desc_NotLike1 varchar(100),

@Desc_NotLike2 varchar(100)

)

AS

 

SET NOCOUNT ON

 

DECLARE @i int

DECLARE @Total int

DECLARE @LoadTestRunId int

 

CREATE TABLE #TEMP(ID int identity(1,1) NOT NULL, LoadTestRunID int not null)

 

INSERT INTO #TEMP(LoadTestRunID)

SELECT LoadTestRunID

FROM dbo.LoadTestRun

WHERE outcome = @Outcome

and (Description NOT LIKE @Desc_NotLike1 or Description NOT LIKE @Desc_NotLike2);

 

SELECT @i = 1

SELECT @Total = COUNT(*) FROM #TEMP

 

WHILE (@i <= @Total)

BEGIN

       SELECT @LoadTestRunId = LoadTestRunId FROM #TEMP WHERE ID = @i

 

       EXEC dbo.PRC_DeleteLoadTestRun @LoadTestRunId = @LoadTestRunId

 

       SELECT @i = @i + 1

END

GO

 

*****End of SP******

 

Crate a Job that runs this SP:

 

Step 1:

Click New

[caption id="attachment_405" align="alignleft" width="875"] Step 2:[/caption]

Enter a step name

Enter the type: Transact-SQL script (T-SQL)

Select the Loadtest DB

Add the command to execute with the filters that you would like to use.

Step 3:

Add the advanced options:

On success actions - Quit the job reporting Success

On failure action - Quit the job reporting failure

Click OK

Click on the schedules tab of your job and set the schedule you would like to use.