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
0 comments
Be the first to start the discussion.