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