Archives for March 2012

TFS 2010 Database size increase

Several of my customers have been complaining the last few months that their Collection databases have increased in size with several hundred percent.

I read Grant Holidays blog ( and used his SQL Script to identify where the problem was.

The increase rate of the database per month:


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 table with most data

— 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))



EXEC [sys].[sp_MSforeachtable] ‘EXEC sp_spaceused ”?”’



name as TableName,


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


ORDER BY CAST(REPLACE(reserved, ‘ KB’, ”) as float) DESC




I started to look around in the databases and found out that the attachments mostly came from test results in Continuous Integration builds. By looking at the Retention policys of the build I found out that the testresults were not removed.

I fixed this in the retention policy by selection All.

The next step was to remove the test results for already deleted build, I could probably do this with the test attachment cleaner described in Grants blog but I decided to do this with the tfsbuild.exe /delete command instead (

Open a Visual Studio command prompt and type the following (replace with your own values) for each build definition.

CD C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDE

tfsbuild.exe delete /collection:http://myserver:8080/tfs/MyCollection /builddefinition:”MyTFSProjectMyBuild definition” /daterange:~2012-03-44 /deleteoptions:TestResults


There is a hotfix you can install to avoid the problem with publishing deployment binaries into TFS, read more about it in Anuttharas blog or get it here





Visual Studio ALM Rangers Dev 11 projects

Microsoft released the Visual Studio 2011 Beta a few weeks ago.

Here are the downloads:

TFS Developer Preview here:

During the last few days the Visual Studio ALM Rangers have released a lot of really nice projects on Codeplex

Shipped projects