Reference : 

Explanation:

A command line tool that only removes the attachments. It does not touch the test runs, which contain the pass/fail data. So, running Test Attachment Cleaner will not impact the record of the test runs.

You need to Test Attachment Cleaner if you:

1. Do manual test runs and want to delete attachments without deleting the test runs
2. Do automated test runs and want to delete attachment for builds which have been deleted already, but the build definition’s retention policy was not set to delete “test results” (because this is turned off by default)

You do not need the Test Attachment Cleaner if you:

1. Have automated test runs triggered by a build, and you have configured the build definition’s retention policy to delete “test results” (which is not set by default)
2. Delete the Test Runs manually (as this deletes the attachments)

 SQL Query to get the attachment type associated with the 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

Download Attachment Cleaner

Command to preview the attachments before delete
tcmpt attachmentcleanup /collection:TfsUrl /teamproject:TeamProjectName /settingsfile:SettingsFile /outputfile:<Output dir>/teamproject.log /mode:preview
Command to delete the attachments
tcmpt attachmentcleanup /collection:TfsUrl /teamproject:TeamProjectName /settingsfile:SettingsFile /outputfile:<Output dir>/teamproject.log

Sample settingsfile

Description:

IT administrators account get populated in the work item "assigned to" field.

By default, [TEAM FOUNDATION]\Valid Users include [TEAM FOUNDATION]\Team Foundation Administrators which holds [BUILT IN]\Administrators.

Workaround:

In the TFS Server Administration Console

1. Select Application Tier and clicked Group Membership. I

2. Double-click on [TEAM FOUNDATION]\Team Foundation Administrators 

3. Remove [BUILT IN]\Administrator

Solution:

SELECT a.ControllerId "Controller ID", a.DisplayName "Controller Name" , a.Status "Controller Status(1 - Active)",

b.AgentId "Agent ID", b.DisplayName "Agent Name", b.Status  "Agent Status(1 - Active)" FROM tbl_BuildController a , tbl_BuildAgent b where a.ControllerId = b.ControllerId

Explanation:

w3wp.exe an Internet Information Services (IIS) worker process is a windows process which runs Web applications, and is responsible for handling requests sent to a Web Server for a specific application pool. W3wp.exe process will not be started automatically after App tier reboot or IIS restart or application pool restart. w3wp.exe will be launched once the first request is made to the TFS Application.

Solution:

1. Try accessing TFS application once http://tfs:8080/tfs. See whether the w3wp.exe is available in "Windows Task Manager" --> "Processes Tab"

2. You can configure Worker process started automatically by adding startMode attribute to TFS application pool to AlwaysRunning in your applicationHost.config

<applicationPools>

   <add name="Default App Pool" startMode="AlwaysRunning" />

</applicationPools>

Solution 2 will work for IIS 7.5 or newer