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:
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
DROP TABLE #t
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.