locked
TFS 2013 - Left over TestResults in Tbl_TestResults RRS feed

  • Question

  • Our database has grown a lot and i am identifying where this data went in to. 

    On of the tables that has grown alot is the Tbl_TestResults, it currently holds 2 million rows.
    I did some random checks and see that there are lines here of builds that are already deleted or even destroyed.
    We have our retention policy (at least now) set to All.

    How can i remove TestResults of builds that have been destroyed. Do i need to do this myself through the API and figure it out, or is there a stored procedure or another tool that i can use ?

    Looking forward to your suggestions.

    Nico

    Monday, August 8, 2016 2:03 PM

Answers

  • Yesterday I found a piece of code that could delete a TestRun through the API, can you confirm this is suitable for use as well ? I'm assuming this call will remove the TestResults behind it as well ?

    TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(new Uri(m_myCollectionUrl));
    
                TestManagementService testManagementService = tpc.GetService<TestManagementService>();
    
                ITestManagementTeamProject teamProject = testManagementService.GetTeamProject("ProjectName");
    
                // Limit by date, so the query doesn't take too long.
                string query = "SELECT * FROM TestRun WHERE IsAutomated=1 AND CreationDate < '07/27/2016'";
                int numTotalToDelete = teamProject.TestRuns.Count(query);
    
                if (numTotalToDelete == 0) { return; }
    
                // Only delete 1000 at a time, to give SQL Server time to breathe (don't ask).
                var runsToDelete = teamProject.TestRuns.Query(query, false).Take(1000);
    
                teamProject.TestRuns.Delete(runsToDelete);
    

    Thursday, August 11, 2016 6:01 AM

All replies

  • Hi Nico,

    Thank you for posting here.

    I'm sorry that there's no API, but you could use the Test Attachments Cleaner to help you according to this blog.

    http://blogs.msmvps.com/vstsblog/2014/03/07/when-to-use-the-test-attachments-cleaner-and-when-not/ 

    Delete a build test result is set at build's retention policies in build definition.

    Also try the following actions that may help you:

    Deleting old workspace

    updated relevant build retention policies

    cleaned up old test result from the MTM


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 9, 2016 9:39 AM
    Moderator
  • Thank you for your reply.

    None of your suggestions will work.

    As i mentioned, the related builds for these test results have already been deleted and probably without checking the Test Results box.

    This was the default setting for a while on our builds and therefor there are alot of builds that didnt delete the test results.

    Now we do want to clean up the test results. 

    The test attachment cleaner will only remove the attachments as is suggested in the name.

    Im looking for a safe way to remove test results from the database when there is no build accosiated with it.
    Using the UI is not an option as there are to many test cases they may be linked to.

    For ease of use, lets say we want to remove all test results older than 30 days of which the build has been deleted.

    Does that make sense ?

    Tuesday, August 9, 2016 9:44 AM
  • Hi nsijtsma,

    I have found a procedure [dbo].[prc_DeleteTestRun] in TFS Collection database. The default value of the parameter @waitDaysForCleanup  is 7, you need to set to 30.

    Hope this could be helpful.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 10, 2016 2:18 AM
    Moderator
  • Thanks, but that wont help me yet. That will only delay the cleaning of Marked as deleted test results I think.

    What I need is to remove test results, that are not marked for deletion yet. They are just orphaned in the database and I need to find a way to remove the orphans.

    I suppose I can write a tool to go over all testRuns and list those that no longer have an active BuildUri linked. But even if I have a list of TestRuns to remove.

    How can I remove a TestRun in the Database with its associated test Results.

    Is there a procedure or API that can do this ?

    Edit:

    I have looked around in the database and tried to match the rows.
    I can see the Tbl_TestRuns is the parent for Tbl_TestResults, each result has a TestRunId set. Since we "only" have 30.000 TestRun rows, its a bit easier to see which should be removed.

    The hard part comes when I try to match a TestRun to a build. I see the TestRun table holds a "Build Number", which is I believe the only pointer to which build it came from, there is no actual BuildUri or BuildId stored.

    The funny part is, the BuildNumber Column in TestRuns does not equal the BuildNumber column in Tbl_Builds. For some reason the BuildNumber column is not clean in Tbl_Builds

    Tbl_TestRun: MsBuild-Project-Main-CI_5.1.194
    Tbl_Builds: MsBuild"Project"Main"CI>5.1.194\

    It looks like the Builds Table has replaced all special characters, making it very hard to do a compare.

    Anyway, any advise would be great. In Short:

    1. Is there an easier way to find TestRuns of which the build has been deleted
    2. How do I safely remove a TestRun including its TestResults from the database.

    • Edited by nsijtsma Wednesday, August 10, 2016 8:28 AM added more info
    Wednesday, August 10, 2016 7:10 AM
  • Hi nsijtsma,

    I have just told you that you could use the [dbo].[prc_DeleteTestRun] procedure in collection database. This procedure doesn't need build information. Because you have already deleted your builds, commands and APIs can't help you delete test results, the best way is using this procedure. But when you run this procedure, I suggest that you backup your database just in case.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 11, 2016 2:29 AM
    Moderator
  • Yesterday I found a piece of code that could delete a TestRun through the API, can you confirm this is suitable for use as well ? I'm assuming this call will remove the TestResults behind it as well ?

    TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(new Uri(m_myCollectionUrl));
    
                TestManagementService testManagementService = tpc.GetService<TestManagementService>();
    
                ITestManagementTeamProject teamProject = testManagementService.GetTeamProject("ProjectName");
    
                // Limit by date, so the query doesn't take too long.
                string query = "SELECT * FROM TestRun WHERE IsAutomated=1 AND CreationDate < '07/27/2016'";
                int numTotalToDelete = teamProject.TestRuns.Count(query);
    
                if (numTotalToDelete == 0) { return; }
    
                // Only delete 1000 at a time, to give SQL Server time to breathe (don't ask).
                var runsToDelete = teamProject.TestRuns.Query(query, false).Take(1000);
    
                teamProject.TestRuns.Delete(runsToDelete);
    

    Thursday, August 11, 2016 6:01 AM
  • Hi,

    I have found another command to delete Test results without build information and I think you could use this command: TFSConfig DeleteTestResults.

    https://www.visualstudio.com/en-us/docs/setup-admin/command-line/tfsconfig-cmd#deletetestresults

    I haven't tested the code above, if I have any results about the code, I will post it back.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 11, 2016 6:10 AM
    Moderator
  • Command availability: TFS "15" and later

    Thursday, August 11, 2016 6:15 AM
  • Hi,

    I have tested the code above. It could delete test results and test runs.

    You could mark the reply I proposed as an answer for this thread.

    It will be beneficial for others who have same questions with you.

    Thank you for your cooperation.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, August 12, 2016 2:31 AM
    Moderator