TFS Statistics update
Long ago I published a post on some of the queries I use to monitor data on our internal servers. Someone asked me to update it for TFS 2008 and I went back and tried them. Only a few needed tweaks. Here's an update on the queries.
Here they are...
use
TfsWorkItemTracking
-- Users with assigned work items:
select
count(distinct [System.AssignedTo]) from WorkItemsAreUsed with (nolock)
-- # of work items:
select
count(*) from WorkItemsAreUsed with (nolock)
-- Work Item versions:
select
count(*) from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x
-- Attached files:
select
count(*) from WorkItemFiles with (nolock) where FldID = 50
-- # of queries:
select
count(*) from StoredQueries with (nolock)
use
TfsVersionControl
-- # of version control users:
select
count(*) from tbl_Identity with (nolock) where IsGroupIdentity = 0
-- # of files(item type != 1)/folders(item type == 1):
select
ItemType, count(*) from tbl_VersionedItem group by ItemType
-- Compresed file size:
select
sum(convert(bigint,OffsetTo - OffsetFrom + 1)) from tbl_Content with (nolock)
-- Uncompressed file sizes:
select
sum(FileLength) from tbl_File with (nolock)
-- Total # of checkins:
select
max(ChangeSetId) from tbl_ChangeSet with (nolock)
-- Pending changes:
select
count(*) from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0
-- Workspaces(type != 1)/Shelvesets(type == 1):
select
type, count(*) from tbl_Workspace with (nolock) group by type
-- Local copies
sp_spaceused
tbl_LocalVersion
use
TfsIntegration
-- Areas & Iterations:
select
count(*) from tbl_nodes with (nolock)
use
TfsActivityLogging
-- Requests by user:
SELECT
sum(ExecutionTime)/1000 AS ExecutionTime, sum(ExecutionCount) AS [Count], IdentityName AS [User]
FROM
tbl_Command with (nolock)
GROUP
BY IdentityName ORDER BY sum(ExecutionTime) DESC
-- Requests by command:
SELECT
sum(ExecutionTime)/1000 AS ExecutionTime, sum(ExecutionCount) AS [Count], Application, Command
FROM
tbl_Command with (nolock)
GROUP
BY Application, Command ORDER BY sum(ExecutionTime) DESC
And yes, I realize I still owe you the TFSServerManager tool that does all of this for you. And yes, I know it's inexusable that it's taken so long. I feel terrible about it and I could waste your time with all the reasons I haven't published it yet. I'm working on it today. I'm not making any promises about when - I've broken too many already.
Brian
