Who deleted my MTM objects?

by XDK 4. November 2014 07:26


All MTM objects information are stored in the TFS collection database table ‘tbl_AuditLog’.


You can query the TFS collection database table ‘tbl_AuditLog’ as below

USE <TFS collection database>

SELECT tbl_AuditLog.AuditId, tbl_AuditLog.DateModified, tbl_AuditLog.Action, tbl_AuditLog.ObjectType, tbl_Project.ProjectName, tbl_AuditLog.AuditIdentity FROM tbl_AuditLog JOIN tbl_Project ON tbl_AuditLog.ProjectId = tbl_Project.ProjectId 

List of Action code:

None = 0
Delete = 1
Reset = 2

List of Object Types:

None = 0
TestRun = 1
TestConfiguration = 2
TestPlan = 3
TestPoint = 5
TestResult = 6
TestVariable = 7
TestResolutionState = 8
TestSettings = 9
Attachment = 10
TestSuite = 11
TestSuiteEntry = 12
TeamProject = 13
TestVariableValue = 14
TestConfigurationVariable = 15
BugFieldMapping = 16
Session = 17
TestController = 18
DataCollector = 19
TestCase = 20
SharedSteps = 21
Other = 1000

You can query the TFS configuration database table ‘tbl_Identity’ for user details

SELECT [Sid],[Id],[DisplayName],[Domain],[AccountName],[MailAddress] FROM [Tfs_Configuration].[dbo].[tbl_Identity] where [Tfs_Configuration].[dbo].[tbl_Identity].[Id] = '<tbl_AuditLog.AuditIdentity value>'



Bulk editing option in MTM 2013

by XDK 30. September 2014 12:59


1. Open the team project web portal
2. Goto Test tab --> select a test suite --> click on “View List” --> click on “Grid” to view the testcases for bulk edit.




About Test Attachment Cleaner

by XDK 30. January 2014 11:37


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

    <SizeInMB GreaterThan="50"/>   


MTM | TFS 2010 | TFS 2012 | TFS 2013

Test Plan/Suite Management through TFS 2013 web access

by XDK 8. January 2014 10:14


You can now add Test Plan and Suite Management to the list of testing features available in Web Access.

Following are the tasks that can be done using web access

  • Create a test plan
  • Add/Create a test suite,static test suite and query-based test suite  
  • Add/Create test cases and Shared steps
  • test case execution
  • Status check after test case execution
  • Change the steps during execution

The differences between the local MTM and Web access is in the collection of diagnostic data (video, screenshots, IntelliTraace, code coverage). The collection of this data is only supported on a locally installed MTM


MTM | TFS 2013 | Visual Studio 2013

Missing suite folder in MTM

by XDK 3. December 2013 07:12


One of the QA Lead reported me that the test suite was lost after the migration from TFS 2010 to TFS 2012. Actually the test suite folder was renamed by the QA Lead and reported as deleted :(

How did I resolved it?

  • Queried the TFS 2012 collection database to get the deleted suite id, But I was not able to identify any test suite delete for the reported project in the collection.

           Select * from tbl_auditlog where ObjectType = 11

  • To get the basic information of test suite like ProjectName, TestPlanId, ParentSuiteId, Title, SuiteType, Query, RequirementId, I queried the TFS 2012 collection database as below

           Select p.ProjectName, s.* from tbl_suite s join  tbl_Project p on s.ProjectId = p.ProjectId

  • I was able to identify the suite information which was reported as deleted. With the suite id, I was able to identify who renamed the test suite and when

           Select LastUpdated,LastUpdatedBy FROM tbl_Suite where [SuiteId] = 'XXXX' 

           Select * from tbl_Identity where Id = ' LastUpdatedBy ' 


MTM | TFS 2010 | TFS 2012

About the author

My name is Xavier Dilip Kumar Jayaraj having 16+ years of IT experience which includes solid experience and depth Knowledge in Application Life Cycle Management, Configuration Management, Implementation and Support using TFS on-premises and Azure DevOps. I have invested in gaining DevOps knowledege to expertise with Cloud Computing providers namely Microsoft Azure and Amazon Web Services in recent years. I am very positive to learn and adapt emerging technologies to client’s environment.

Microsoft Certified: Azure Administrator Associate

Microsoft Certified: Azure DevOps Engineer Expert

DevOps Engineer Certificate Program | Transcript 

OTP-AWSD5: AWS IoT: Developing and Deploying an Internet of Things

[PCEP-30-01] PCEP – Certified Entry-Level Python Programmer

Quotes I Like

"Failure will never overtake me if my determination to succeed is strong enough."  - Dr. APJ. Abdul Kalam

"Always be yourself, express yourself, have faith in yourself, do not go out and look for a successful personality and duplicate it." - Bruce Lee

"Technology is just a tool. In terms of getting the kids working together and motivating them, the teacher is the most important." - Bill Gates

"Innovation distinguishes between a leader and a follower." - Steve Jobs



The information provided here is based on my expreriences, troubleshooting and online/offline findings. It can be used as is on your own risk without any warranties and I impose no rights.