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**********
@Outcome = 'Aborted',
@Desc_NotLike1 = '%TOR%',
@Desc_NotLike2 = '%Save%'
CREATE PROCEDURE dbo.Prc_DeleteLoadTestRun_Wrapper
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)
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)
SELECT @LoadTestRunId = LoadTestRunId FROM #TEMP WHERE ID = @i
EXEC dbo.PRC_DeleteLoadTestRun @LoadTestRunId = @LoadTestRunId
SELECT @i = @i + 1
*****End of SP******
Crate a Job that runs this SP:
[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.
Add the advanced options:
On success actions - Quit the job reporting Success
On failure action - Quit the job reporting failure
Click on the schedules tab of your job and set the schedule you would like to use.