Team Foundation Server Useful SQL Queries

Below mentoned TFS SQL queries could be helpful in investigations, gathering important information from project collection database.

Please note that some of the queries execution may impact TFS performance and it is not advised to make any changes via SQL backend.

TFS Version: TFS 2012 or higher

Top 10 Commands with highest average response time (in last 7 days)

USE [Tfs_DefaultCollection]

SELECT TOP 10 Application, Command, ROUND(SUM(CAST(ExecutionTime ASfloat)/1000000)/SUM(ExecutionCount),3)AS ResponseTimeInSeconds

FROM tbl_Command WITH (NOLOCK)

WHERE StartTime >DATEADD(DAY,-7,GETUTCDATE())

GROUP BYApplication,Command

ORDER BYSUM(CAST(ExecutionTime ASfloat)/1000000)/SUM(ExecutionCount)DESC

Number of files in a Team Project Collection (TPC)

 USE [Tfs_DefaultCollection]

SELECT OwnerId, COUNT(*)AS Cnt,SUM(FileLength)as filelengthSum,SUM(CompressedLength)AS CompressedLengthSum

FROM tbl_File WITH (NOLOCK)

WHERE PartitionId=1

GROUP BY ownerid

Get list of failed Jobs (TFS interal scheduled jobs)

USE [Tfs_DefaultCollection]

SELECT d.JobName, q.QueueTime as NextRunTimeUTC, DATEDIFF(SECOND,GETUTCDATE(), q.QueueTime)AS NextRun,

                 JobState, ExecutionStartTime as ExecutionStartTimeUTC, StateChangeTime, q.QueuedReasons, q.QueueFlags, shp.MachineName as AgentName,

                shp.StartTime as AgentStartTimeUTC,jh.ResultMessage

FROM tbl_jobqueue q

JOIN tbl_JobDefinition d ON d.JobId=q.JobId

JOIN tbl_JobHistory jh ON JH.JobId = q.JobId

LEFT JOIN tbl_ServiceHostProcess shp ON q.AgentId=shp.ProcessId

WHERE Result <> 0
ORDER BY jh.StartTime DESC

Get List of Workspaces not accessed in last 90 days

USE [Tfs_DefaultCollection]

SELECT COUNT(*)

FROM tbl_Workspace ws WITH (NOLOCK)

JOIN tbl_LocalVersion lv WITH (NOLOCK)

ON lv.WorkspaceId = ws.WorkspaceId

WHERE ws.Type = 0 and ws.LastAccessDate < DATEADD(DAY,-90,GETUTCDATE())

Number of TFS Builds Executed in Last 7 Days

USE [Tfs_DefaultCollection]

SELECT P.ProjectName, BD.DefinitionName ,B.[BuildNumber] , B.[StartTime], B.[FinishTime] , BQ.Quality ,B.[BuildStatus] ,B.[CompilationStatus] ,B.[TestStatus] ,

B.[LastChangedOn] ,B.[KeepForever] ,B.[BuildUri] ,B.[Reason] ,B.[Deleted] ,B.[DeletionStatus] ,B.[BuildId] ,B.[ContainerId]

FROM [tbl_Build] B WITH (NOLOCK)

LEFT OUTERJOIN tbl_BuildDefinition BD WITH (NOLOCK) ON B.DefinitionID = BD.DefinitionId

LEFT OUTERJOIN tbl_BuildQuality BQ WITH (NOLOCK) ON BQ.QualityID = B.QualityId

LEFT OUTERJOIN [tbl_BuildGroup] BG WITH (NOLOCK) ON BG.GroupId = BD.GroupID

LEFT OUTERJOIN tbl_Project P WITH (NOLOCK)ON P.ProjectUri = BG.TeamProject

WHERE B.PartitionId = 1 AND BD.PartitionId = 1

AND B.[StartTime] > DATEADD(DAY,-7,GETUTCDATE())

ORDER BY B.StartTime

Get List of Application Tier Server(s):

SELECT * FROM [Tfs_Configuration].[dbo].[tbl_ServiceHostProcess] WITH (NOLOCK)