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 (http://blogs.msdn.com/b/granth/archive/2011/02/12/tfs2010-test-attachment-cleaner-and-why-you-should-be-using-it.aspx) and used his SQL Script to identify where the problem was.

The increase rate of the database per month:

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

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

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 (http://msdn.microsoft.com/en-us/library/ms244360.aspx).

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 http://blogs.msdn.com/b/anutthara/archive/2011/10/30/gsjgd.aspx or get it here http://support.microsoft.com/kb/2608743.

 

 

 

 

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: http://www.microsoft.com/download/en/details.aspx?id=27542

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

Shipped projects