1

We're using TFS 2012 since 2013. In the mean time the database has increased up to ~60GB. This has several drawbacks like increased backup time, wast of drive space, ...

After having a look into to 'Disk Usage by Top Tables' report I recognized that the tbl_BuildInformation table has been blown up. 46GB out of 60GB is used to store/archive past build information (build log an so on). And we have absolutely no purpose to use this information any more.

Therefore I followed the suggestions in the MSDN forums how do clean these. I applied the two commands (tfsbuild.exe delete ... and tfsbuild.exe destroy ...) to our TFS server. After almost 1.5h the cleanup has been successfully done. The respecting table hasn't changed, though. But almost all entries are orphans with no reference to any further information anymore.

Regarding other sources this behavior is common and intended. But in my opinion absolutely nonsense and useless. I did this steps to get a clean and shrinked database.

Any ideas or recommendations how to get rid of this? Actually I could delete the orphaned entries in the database with a SQL query but experts advise strictly against that concerning unexpected behavior. What are your experiences?

dannyyy
  • 111
  • 4
  • Editing the TFS database in any way will void your warranty... – MrHinsh - Martin Hinshelwood May 22 '15 at 20:38
  • 1
    I've seen this too. At first I thought it was because I had renamed the build definition and the test runs had somehow become orphaned from the parent builds. Now that the builds have been destroyed I'm pretty much stuck. Am going to try posting on the MSDN forums – Stephen Connolly Aug 20 '15 at 12:20

1 Answers1

2

You have to wait until the "Build Information Cleanup Job" runs (by default it runs every two days). This is the job which cleans up tbl_buildInformation after you run TFSBuild.exe delete and TFSBuild.exe destroy.

You can check its schedule with (in PowerShell):

$collection = "http://yourservername:8080/tfs/YourCollection" # change this to the URL of your team project collection
$pathToAss2 = "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\ReferenceAssemblies\v2.0"
$pathToAss4 = "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\ReferenceAssemblies\v4.5"
Add-Type -Path "$pathToAss2\Microsoft.TeamFoundation.Client.dll"
Add-Type -Path "$pathToAss2\Microsoft.TeamFoundation.Common.dll"
Add-Type -Path "$pathToAss2\Microsoft.TeamFoundation.WorkItemTracking.Client.dll"
Add-Type -Path "$pathToAss2\Microsoft.TeamFoundation.VersionControl.Client.dll"
Add-Type -Path "$pathToAss4\Microsoft.TeamFoundation.ProjectManagement.dll"
$tpc =  [Microsoft.TeamFoundation.Client.TfsTeamProjectCollectionFactory]::GetTeamProjectCollection($collection)
$jobService = $tpc.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamFoundationJobService])
$job = $jobService.QueryJobs() | Where-Object {$_.Name -eq "Build Information Cleanup Job"}
$job
$jobService.QueryLatestJobHistory([Guid[]] @($job.JobId))

To queue the job for running immediately, run the previous commands followed by the following command:

$jobService.QueueJobNow([Guid] $job.JobId, $false)

To change the schedule, run:

$interval = 172800 # change this to the number of seconds between each run; 172800 = 2 days
$job.Schedule[0].Interval = $interval # there is only one schedule for the build information clean-up job, we set its interval
$jobService.UpdateJob($job)

To actually see when the next run of this job is scheduled, you have to go to the DB and run the following query (the query is not completely correct because it shows in the columns "ScheduledTime" and "Interval" the information of Tfs_YourCollection for all listed collections, the other information is correct, especially the QueueTime):

USE Tfs_YourCollection
SELECT S.JobId, D.JobName, S.ScheduledTime, S.Interval, CQ.QueueTime, SH.Name
FROM tbl_JobSchedule S
LEFT OUTER JOIN tbl_JobDefinition D ON D.JobId = S.JobId
LEFT OUTER JOIN [Tfs_Configuration].dbo.tbl_JobQueue CQ ON CQ.JobId = S.JobId
LEFT OUTER JOIN [Tfs_Configuration].dbo.tbl_ServiceHost SH ON SH.HostId = CQ.JobSource
WHERE D.JobName = 'Build Information Cleanup Job'

See http://blogs.msdn.com/b/chrisid/archive/2010/02/15/introducing-the-tfs-background-job-agent-and-service.aspx and http://blogs.msdn.com/b/granth/archive/2013/02/13/tfs2012-what-are-all-the-different-jobs-built-in-to-tfs.aspx for more information.

Glad
  • 21
  • 2