Tuesday 29 June 2010

Cleaning VSTS test results database

Cleaning test results database

Default installation of Visual Studio comes with SQL Server Express database which is used for storing load test data.

With every new test run the Visual Studio results database gets bigger and bigger and might eventually reach a 4BG which is the maximum database size in the SQL Server Express edition. When that happens no more tests can be run.

This problem can be overcame by either removing some of the historical results or renaming LoadTest database to something else and recreating the schema.

Removing test results

Removing no longer needed results will usually take longer as the SQL stored procedure needs to remove records from many related tables. The advantage of using this method is that there will only be one database containing only the important test results.

All operations described on that page require SQL execution engine such as Microsoft Management Studio.

Removing individual results

Test runs could be deleted from within Visual Studio by opening "Manage Test Results", connecting to a controller (or local instance of Visual Studio), selecting the result and clicking Remove button.

Test results could also be deleted directly from a database by executing Prc_DelereLoadTestRun stored procedure on the LoadTest database.

Exec Prc_DeleteLoadTestRun 1

In order to find more information about runs execute this SQL statement:

Select * from LoadTestRun

Removing old results based on date

Following code (which comes from Bill Barnett blog at http://blogs.msdn.com/billbar/archive/2006/02/09/528900.aspx) is a more robust SQL script which remove all test run results which are older then 14 days. This number could be changed depending on how many results needs to be deleted.

USE LoadTest
DECLARE @LoadTestRunId int
DECLARE OldLoadTestsCursor CURSOR FOR
SELECT LoadTestRunId FROM LoadTestRun
 WHERE datediff(dd, StartTime, getdate()) > 14

OPEN OldLoadTestsCursor
FETCH NEXT FROM OldLoadTestsCursor INTO @LoadTestRunId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Prc_DeleteLoadTestRun @LoadTestRunId
FETCH NEXT FROM OldLoadTestsCursor INTO @LoadTestRunId
END
CLOSE OldLoadTestsCursor
DEALLOCATE OldLoadTestsCursor


Recreating results database

This approach is faster to use since the database data remains the same and only the database name gets changed.

Ones connected to a database rename the LoadTest database to something else and:

  1. Open Visual Studio command prompt
  2. For Visual Studio 2008 type: cd "C:\Program Files\Microsoft Visual Studio 9\Common7\IDE"
  3. Type: SQLCMD /S localhost\sqlexpress /i loadtestresultsrepository.sql

For more complete guide to setting up LoadTest database please refere to this MSDN article: http://msdn.microsoft.com/en-us/library/ms182600.aspx

2 comments: