TFS tbl_Content started growing very fast after using VNext build

Question

We have been using the old style (XAML) of builds until a month ago and then started using vNext builds. After that I noticed that tbl_Content table in TFS database started growing really fast. For instance, in the last 8 hours it grew 10 GB and I can't figure out what why it is doing that. Does any one know what it is?

The reason I am saying it is the vNext build is because I noticed it started growing after we upgraded, but I way be wrong. I hope it is not trying to store my build output or something like that. Is there way to know what is in the tbl_Content table? Can they be deleted? Or is there way to delete some things from that table without messing up TFS?

Answer 1

TFS Databases grows for all sorts of reasons. Some common sources of runaway growth include using build or release automation without configuring appropriate retention policies, creation of a small number of very large items (work items, version control files, etc.) by humans or tooling, and so forth. We have work on the backlog to make the distribution of space in TFS databases more discoverable.

You could try to use a sql script such as below to show a increase of the tbl_Content over the last months:

select DATEPART(yyyy, CreationDate) as [year],
  DATEPART(mm, CreationDate) as [month],
  count(*) as [count],
  SUM(DATALENGTH(Content)) / 1048576.0 as [Size in Mb],
  (SUM(DATALENGTH(Content)) / 1048576.0) / count(*) as [Average Size]
from tbl_Content
group by DATEPART(yyyy, CreationDate),
    DATEPART(mm, CreationDate)
order by DATEPART(yyyy, CreationDate),
    DATEPART(mm, CreationDate)

It's able to know to look at the distribution of "owners" for the data in tbl_Content through a SQL query. Detail steps please refer Aaron Hallberg's reply in this similar question: TFS Database size

To reduce the size of the tbl_Content table, you could refer to this blog: TFS tbl_Content Table and Database growth out of control,it can be summed up in three steps in general:

1. Clean some old workspaces that you doesn't need any more.
2. Run the tf destory command to delete those unnecessary source files permanently.
3. Using TFS power tool to clean Test attachments and test results.

Answer 2

I was googling around and tried doing all kinds of things to see what is making the problem. Other problem is there is no documentation about the TFS database schema. After spending almost 10 hours with playing with TFS Database I almost become a TFS DB pro. Querying around the tbl_Content, tbl_FileMetadata and tbl_FileType helped me little to figure what exactly is making the problem.

So at the end removing the /v:diag from MSBuild arguments fixed my issue. I am not sure what all it was putting to the database, but each build was adding close to 1GB data to the database.