How to Shrink the TFS 2017 Fast Growing tbl_content Table

Question

We use TFS 2017 CI / CD pipelines, and it works great. However, the TFS 2017 databases grow averaging around 1GB per day. One database grows from 10GB to 44GB as of 10/23/2018. The growth slowly becomes unsustainable for us. We already adjusted retention policy to minimum.

Researched and read at least 30 articles. Here are some relevant articles:

TFS tbl_Content started growing very fast after using VNext build

https://mattyrowan.com/2014/04/02/need-help-tfs-tbl_content-table-and-database-growth-out-of-control/

https://developercommunity.visualstudio.com/content/problem/63712/tfs-database-size.html

Here are what I did so far:

1. Reviewed again and again the retention policies, and reduced to minimum (1 day 1 copy). Adjusted 'Keep Deleted' for 10 days.

2. Uncheck the 'Retain Build' box in release definition

3. Run the scripts from three articles mentioned above, and found:

   a) FileContainer, has 149176 number of files, 43GB, (34GB compressed)

   b) FileContainerOwner: Build, 29GB

So the main cause of the growth is Build (and artifacts).

My question is how to shrink the database size down?

I look at the tabs 'History' and 'Deleted' under build definitions.

1. Some records in 'History' are locked with 'Retained by release'. I can click on records and delete. But it doesn't do anything. The records are still there.

2. All records in 'Deleted' are still there.

So back to my question again, how do I delete these records so that the space can be reclaimed?

Thanks.

Answer 1

After reset the RetainedByRelease to false and waited for at least 24 hours, the growth spurt stopped and entries in tbl_content were removed daily.

So in a summary, I also did this:

4. Reset the RetainedByRelease to false using TFS REST API after nuget Microsoft.VisualStudio.Services.Client

Special thanks to these two threads:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/5f649821-b1bf-4008-bba9-0c960e124abb/tfs-releasemanagement-vnext-quotthis-build-has-been-retained-by-a-releasequot-issue?forum=tfsbuild

Trying to get list of TFS users trough client library

The full source code to help fellow developers:

using Microsoft.TeamFoundation.Build.WebApi;
using Microsoft.TeamFoundation.Core.WebApi;
using Microsoft.VisualStudio.Services.Client;
using Microsoft.VisualStudio.Services.Common;
using Microsoft.VisualStudio.Services.WebApi;
using System;

namespace TfsRestAPIs
{
    public class RestAPI
    {
        public static void UpdateRetainedByRelaseToFalse()
        {
            Uri tfsURI = new Uri("http://TFS2017:8080/tfs/YourProjectCollection");
            VssCredentials creds = new VssClientCredentials();
            creds.Storage = new VssClientCredentialStorage();
            VssConnection connection = new VssConnection(tfsURI, creds);
            var projectClient = connection.GetClient<ProjectHttpClient>();
            var projects = projectClient.GetProjects().Result;
            var buildClient = connection.GetClient<BuildHttpClient>();            

            foreach (var project in projects)
            {
                Log(project.Name);
                if (project.Name == "YourProjectName")
                {
                    var builds = buildClient.GetBuildsAsync(project.Id).Result;
                    foreach (Build build in builds)
                    {
                        if (build.BuildNumber.StartsWith("YourSearchCondition"))
                            try
                            {                                
                                if (build.RetainedByRelease.Value)
                                {
                                    Log(build.BuildNumber + "'s RetainedByRelease=true");
                                    build.RetainedByRelease = false;
                                    var res = buildClient.UpdateBuildAsync(build, build.Id).Result;
                                    Log("  --> RetainedByRelease is set to " + res.RetainedByRelease.Value);
                                }
                            }
                            catch (Exception e)
                            {
                                Log(build.BuildNumber + ":" + e.Message);
                            }
                    }
                }
            }
        }

        private static void Log(string msg)
        {
            Console.WriteLine(msg);
        }
    }
}