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

select count(distinct [System.AssignedTo]) from WorkItemsAreUsed with (nolock)

select count(*) from WorkItemsAreUsed with (nolock)

select count(*) from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x

select count(*) from WorkItemFiles with (nolock) where FldID = 50

select count(*) from StoredQueries with (nolock)

use TfsVersionControl

select count(*) from tbl_Identity with (nolock) where IsGroupIdentity = 0

select ItemType, count(*) from tbl_VersionedItem group by ItemType

select sum(convert(bigint,OffsetTo - OffsetFrom + 1)) from tbl_Content with (nolock)

select sum(FileLength) from tbl_File with (nolock)

select max(ChangeSetId) from tbl_ChangeSet with (nolock)

select count(*) from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0

select type, count(*) from tbl_Workspace with (nolock) group by type

sp_spaceused tbl_LocalVersion

use TfsIntegration

select count(*) from tbl_nodes with (nolock)

use TfsActivityLogging

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

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