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