TFS2010: Update Activity Logging Cleanup Interval

Every command that a user executes in TFS is logged to the database. This is very useful for investigating performance issues and other things. I’ve blogged before about how to query this table for TFS2008. Those same queries work for TFS2010 as well.

By default, each night a job runs that deletes log entries older than 14 days. If you want to change that interval, you’ll need to update the cleanup job settings in each collection.

Here’s a PowerShell script that will do this for you.

  • If you are not running it on the AT itself. You will need Team Explorer 2010 installed.
  • You will need PowerShell installed & run: set-executionmode unrestricted

You will need to update the highlighted values (server URL & retention period).

$a = [Reflection.Assembly]::Load("Microsoft.TeamFoundation.Client, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

#Update configuration URL as necessary
$configServer = new-object Microsoft.TeamFoundation.Client.TfsConfigurationServer "http://localhost:8080/tfs/"

$collectionService = $configServer.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamProjectCollectionService])

$collectionService.GetCollections() | %{
        $collectionName = $_.Name
        $collectionId = $_.Id

        trap [Microsoft.TeamFoundation.TeamFoundationServiceUnavailableException]
           write-warning ("Skipping project collection "+ $collectionName +" ("+ $collectionId +"): "+ $_.Exception.Message)
           write-host ("* Operating on project collection "+ $collectionName +" ("+ $collectionId +").")

           $projectCollection = $configServer.GetTeamProjectCollection($collectionId)
           # Check that we can access the team project collection.
           #Get the job service
           $jobService = $projectCollection.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamFoundationJobService])

           #Get the job for the current collection
           $job = $jobService.QueryJobs() | where { $_.Name -eq "Team Foundation Server Activity Logging Administration" }

           #Output the current setting
           Write-Output "Current setting:"

           #Update the current setting
           $job.Data.SqlStatement.InnerXml = "<string>EXEC prc_PruneCommands @maxAgeDays = 30</string>"
           Write-Output "New setting:"

           Write-Output "Saving job definition... "


Comments (4)
  1. MagnusTim says:

    Hi Grant,

    I have used this script before but for one of my customers I get this:

    ForEach-Object : Exception calling "UpdateJob" with "1" argument(s): "Access Denied: tfssetup needs the following

    permission(s) on the resource AllJobs to perform this action: Manage background jobs"

    Do you have any suggestions? The setup account has TFS admin rights and we are using TFS 2010.


  2. @MagnusTim – Unfortunately this method no longer works once you have applied either TFS2010 SP1 + Security patch or upgraded to TFS2012. This was a security-related change to prevent a situation where a remote attacker can add/update a job to execute arbitrary SQL.

    In TFS2012, you will receive the error: "TF400444: Jobs targeting the Microsoft.TeamFoundation.JobService.Extensions.Core.GenericSqlJobExtension extension can't be added or updated from the client. Only servicing operations running on the server can define these jobs."

    This was a security-related change to prevent a situation where a remote attacker can add/update a job to execute arbitrary SQL.

    I'm told that there is a change in >TFS2012 that will shift this cleanup interval setting to a TFS registry setting.

  3. MagnusTim says:

    Hi Grant,

    Thanks for your answer! I guess we have to do it in another way then.

    Is the update interval stored the registry or the database for TFS 2010 and TFS 2012?


  4. @MagnusTim – It's stored in tbl_JobDefinition for each team project collection. It's not recommended/supported to change the database directly. But you should be able to work it out in this case.

Comments are closed.

Skip to main content