Development Tasks: Expanding TFS 2010 Database post Lab Management setup

Monday Morning:

I usually love Mondays. A brand new week, so much to look forward to, UNLESS you get an angry email from your System Administrator & you know you have a plethora of other development tasks you need to finish this week.

The Email:

Findings:

After some digging around, here is what I had discovered:

Our Team Foundation Server database [it uses Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)]
The highest recorded growth was in the following few tables

Post our initial investigation, it became clear that TFS had decided to store binary build objects in the attachment tables after we had set-up Lab Management & decided to use Test Driven Development (Unit Tests run on Gated Check Ins, Unit + Integration Tests run on Nightly Builds & Unit + Integration + UI Tests run on Staged & Release Builds.) I wrote the following scripts:

It was interesting to see that on every test run (triggered by a Gated Check In or a Release/Staged build) we were adding > 250 files to the Attachment Content table. Yikes!! This was looking more and more like an episode of "When good servers go bad!"

Test Attachment Cleaner:

A couple of interesting google searches later I discovered the Test Attachment Cleaner [http://visualstudiogallery.msdn.microsoft.com/3d37ce86-05f1-4165-957c-26aaa5ea1010/]

The MSI installs itself to "C:\Program Files (x86)\Microsoft\Test Attachment Cleaner". The readme file explained it all ...

In Dev10, with the introduction of Visual Studio Test Professional 2010 & Visual Studio Premium/Ultimate 2010, testers can author manual and automated Test cases, configure the different diagnostic data collectors (as part of Test Settings), associate the Test Settings with Test Plan/Suites and then execute these test cases as part of Test Runs. The execution of a Test Run (whether automated or manual) generates a bunch of diagnostic data, which may be captured either automatically by the system or manually by the tester. This diagnostic data is critical in eliminating the “no repro” bug scenarios between the testers and developers. However, the downside of this rich diagnostic data captures is that the system/user generated diagnostic data, over a period of time, can grow at a rapid pace and start taking up database space.

With Dev10, the database admin has little or no control over what data gets attached as part of Test Runs – i.e., there are no policy settings he can control to limit the size of the data capture OR no retention policy to determine how long to hold this data before initiating a cleanup. In such scenarios, the Admin has no mechanism to:

a. Determine which set of diagnostic captures is taking up how much space AND
b. Reclaim the space for runs which are no longer relevant from business perspective.

The “Test Attachment Cleaner” tool fills this void by serving both the above points.

I ran the following commands on each project:

tcmpt.exe attachmentcleanup /collection:http://localhost:8080/tfs/TFS2008_Migrated_Projects /teamProject: ProjectName /settingsFile:.\SampleSettings\CustomScenario.xml /mode:Preview

tcmpt.exe attachmentcleanup /collection:http://localhost:8080/tfs/TFS2008_Migrated_Projects /teamProject:ProjectName /settingsFile:.\SampleSettings\CustomScenario.xml /mode:Delete

The CustomScenario xml file I used was as follows:

Additional Patches & Test Settings:

Additionally, it turns out that we needed the following patches/updates/hotfixes applied to TFS:

Microsoft Team Foundation Server 2010 Service Pack 1: http://support.microsoft.com/kb/2182621
Cumulative update package 2 for Visual Studio Team Foundation Server 2010 Service Pack 1: http://support.microsoft.com/kb/2643415#appliesto
A hotfix that can reduce the size of the test data saved to the TFS database is available for Team Foundation Server 2010 Service Pack 1: http://support.microsoft.com/kb/2608743