Control\Reduce TFS DB Size

29/06/2014

Today I received an email from the company DBA saying that out TFS Backup failed because “There is not enough space on the disk”.

First I’ve opened SSMS to understand how big is our TFS DB and I…….. What????? I knew that we’re getting bigger from day to day (more and more groups are joining to start working with TFS) but 633GB???

image

TFS DB Size growth usually comes from Attachments and Source Control, So I tried to understand which Team Projects are taking most of the space.

I need to understand what type of attachments are taking all that space, again per Team Project.

Select projects.ProjectName, SUBSTRING(attachments.filename, len(attachments.filename)-CHARINDEX('.',REVERSE(attachments.filename))+2,999) as Extension, sum(f.compressedlength)/1024/1024 as SizeInMB 
From tbl_Attachment as attachments 
INNER JOIN tbl_File as f on attachments.TfsFileId=f.fileid
INNER JOIN tbl_TestRun as tr on attachments.TestRunId = tr.TestRunId
INNER JOIN tbl_Project as projects on tr.ProjectId = projects.ProjectId
Group by projects.ProjectName, SUBSTRING(attachments.filename,len(attachments.filename)-CHARINDEX('.',REVERSE(attachments.filename))+2,999)
Order by sum(f.compressedlength) desc

image

Now I can see I have two Team Projects with total of 200GB just for attachments just for wmv files.

The idea now is to delete all attachments from Work items that are closed or removed, in order to accomplish that I’ve download Test Attachment Cleaner that is part of TFS 2013 Power Tools.

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.

In Visual Studio 2013, the database administrator has little or no control over what data gets attached as part of Test Runs. For example, she has no policy settings to limit the size of the data capture, or how long to hold the data before initiating a cleanup. This tool addresses these issues by:

  1. Determining which set of diagnostic captures is taking up how much space AND
  2. Reclaiming the space for runs which are no longer relevant from business perspective.

I’ve created my own search criteria – search all file types, with attachment size greater than 10MB, exclude Bugs in New,Approved, Committed states.

<DeletionCriteria>
  <TestRun />
  <Attachment>
    <SizeInMB GreaterThan="10" />    
  </Attachment>
  <LinkedBugs>
    <Exclude state="New" />
    <Exclude state="Committed" />
    <Exclude state="Approved" />
  </LinkedBugs>
</DeletionCriteria>

After you create the desire settings file run the following command:

tcmpt attachmentcleanup /collection:TfsUrl /teamproject:TeamProjectName /settingsfile:SettingsFile /outputfile:%temp%/teamproject.log /mode:preview

Make sure to start with Preview option, this operation will display all attachments applied to your search criteria and the total size, review the output file and once you ready to delete all those attachments run the same command with one change – instead of preview – delete.

tcmpt attachmentcleanup /collection:TfsUrl /teamproject:TeamProjectName /settingsfile:SettingsFile /outputfile:%temp%/teamproject.log /mode:delete

Once it’s done it recommended to run shrink command on the Collection Db

Enjoy.

Add comment
facebook linkedin twitter email

Leave a Reply

2 comments

  1. Pingback: TFS Performance Tips - Cache - ► Shai Raiten's Blog

  2. Pingback: TFS Cleaner Tool - ► Shai Raiten's Blog