Cleanup redundant test impact data

In this article, I am going to talk about how to get rid of redundant test impact data from Team Foundation Server. The test impact data is mostly stored in tbl_testcodesignature table in project collection database. This table essentially keeps the mapping between a testresult and the impacted CodeSignatures from the product dll. Normally a testcase will use lot of codesingnatures from product so the size of this table grows up in million of rows. Test impact data is associated with a test run which in turn is associated with a particular build. So when a build gets deleted, all the runs associated with a build also gets deleted. As part of run deletion , we delete test impact data from tbl_testCodeSignature table also. So one approach to keep check on size of test impact data table is to delete redundant builds which have lot of test impact data.

You can find the pattern of data associated with tbl_testcodesignature table with builds by running  the following query. This sql query gives an idea of how much rows of  tbl_testCodeSignature table belongs to a particular build. This query is for dev10 TFS and should be run at project collection database.

select tbc.BuildUri, COUNT(*)

from tbl_TestCodeSignature tc

join tbl_TestRun tr

on tc.TestRunId = tr.TestRunId

join tbl_buildconfiguration tbc

on tbc.BuildConfigurationId
= tr.BuildConfigurationId

group by tbc.BuildUri

 

If there are unused builds with large amount of test impact data, you can delete those builds using TfsBuild.exe commandline. Make sure you specify testresults as part of build deletion option or you can specify ALL.