[Update: 1 Nov 2011] There is an update available for TFS 2010 SP1 that prevents many of the large test attachments being stored in the database in the first place. You can download it from KB2608743 or read more about it on Annuthra's blog post: Reduce the size of test data in VS 2010 by avoiding publishing deployment binaries into TFS.

The execution of a Test Run in Team Foundation Server 2010 (whether automated or manual) generates a bunch of diagnostic data, for example, IntelliTrace logs (.iTrace), Video log files, Test Result (.trx) and Code Coverage (.cov) files. This diagnostic data is critical in eliminating the “no repro” bug scenarios between the testers and developers.

However, the downside of these rich diagnostic data captures is that the volume of the diagnostic data, over a period of time, can grow at a rapid pace. The Team Foundation Server administrator has little or no control over what data gets attached as part of Test Runs. There are no policy settings to limit the size of the data capture and there is no retention policy to determine how long to hold this data before initiating a cleanup.

A few months ago, the Test Attachment Cleaner for Visual Studio Ultimate 2010 & Test Professional 2010 power tool was released which addresses these shortcomings in a command-line tool.

Over the last 18 months, the TFS 2010 Dogfood server that we call ‘Pioneer’ has been slowly accumulating test attachments. Recently, the size of the attachments (680GB) eclipsed the size of version control content (563GB) and that Test Attachments older than 6 months represented over 400GB of storage.

After downloading and installing the tcmpt.exe tool, there are a five sample settings files in: C:\Program Files (x86)\Microsoft\Test Attachment Cleaner\SampleSettings

1. Scenario#1: Identify list of attachments taking up database space of more than 1 GB per attachment
2. Scenario#2: View/Delete IntelliTrace log files over a size of 500 MB
3. Scenario#3: View/Delete all Video log files with Test run creation date older than specific date with no active bugs
4. Scenario#4: View/Delete all TRX & COV log files for test runs that happened between 30 and 90 days in age & do not perform the Linked Bugs lookup query
5. Scenario#5: View/Delete all custom/user attachments over size of 5 MB with no active or resolved bugs on test runs between 2 dates

After checking with the test managers and users of the system, we got approval to remove the old content and we ended up creating our own settings file:

<!-- View/Delete all attachments on test runs older than 6 months, that are not linked to active bugs -->
<DeletionCriteria>
  <TestRun>
    <AgeInDays OlderThan="180" />
  </TestRun>
  <Attachment />
  <LinkedBugs>    
     <Exclude state="Active" />
  </LinkedBugs>
</DeletionCriteria>

Then we ran the command-line tool with the following parameters:

TCMPT.exe attachmentCleanup /collection:http://localhost:8080/tfs/CollectionName /teamproject:TeamProjectName /settingsFile:OlderThan6Months.xml /mode:delete

The following charts show the result after the tool ran for over 36 hours:

Warning: Running queries on the operational stores is not recommended and you should run them on a pre-production/backup server if possible. In this case, this particular query is read-only and returns very quickly, so the impact to the overall system is low. Also, since the schema is intentionally not documented or supported, there are no promises that it won't change for a future release (in fact, this table already has changed as part of making TFS work on Azure).

To see the growth of your Test Case attachments over time, you can use the following SQL query on each of your collection databases:

SELECT 
  DATEADD(month,DATEDIFF(month,0,creationdate),0) as [Month],
  SUM(CompressedLength) / 1024 / 1024 as AttachmentSizeMB
FROM tbl_Attachment WITH (nolock)
GROUP BY DATEADD(month,DATEDIFF(month,0,creationdate),0) 
ORDER BY DATEADD(month,DATEDIFF(month,0,creationdate),0)

The result will be something like this:

To see the size of all the tables in your collection databases, you can use the following SQL query:

-- Table rows and data sizes
CREATE TABLE #t (     [name] NVARCHAR(128),    [rows] CHAR(11),    reserved VARCHAR(18),     data VARCHAR(18),     index_size VARCHAR(18),    unused VARCHAR(18))
GO

INSERT #t
EXEC [sys].[sp_MSforeachtable] 'EXEC sp_spaceused ''?'''
GO

SELECT
    name as TableName,
    Rows,
    ROUND(CAST(REPLACE(reserved, ' KB', '') as float) / 1024,2) as ReservedMB,
    ROUND(CAST(REPLACE(data, ' KB', '') as float) / 1024,2) as DataMB,
    ROUND(CAST(REPLACE(index_size, ' KB', '') as float) / 1024,2) as IndexMB,
    ROUND(CAST(REPLACE(unused, ' KB', '') as float) / 1024,2) as UnusedMB
FROM #t
ORDER BY CAST(REPLACE(reserved, ' KB', '') as float) DESC
GO

DROP TABLE #t
GO

The result will be something like the following table. The names can be a little confusing (which is unlikely to change, since the schema isn’t documented/supported). I’ve added a column that gives a brief description of what the tables are used for.