Senior Service Engineer, Microsoft Visual Studio Team Foundation Service
Update Dec 2012: This report pack works unmodified on TFS2012 as well as TFS2010.
One of the key components of TFS is the Data Warehouse, which is made up of a relational database and an Analysis Services cube. In general, people don’t have a problem with the performance or operation of our Data Warehouse. However there were two classes of problems that you’re likely to run into as your servers grow larger:
Internally at Microsoft we started noticing some problems after we upgraded TFS2008 servers and consolidated them onto larger TFS2010 servers as new Team Project Collections (TPCs). There is a constraint (or feature, depending how you look at it) in the system that you can only have a single data warehouse per instance of TFS2010. This feature enables you to do cross-project reporting for gathering data across all the projects in all collections on that server. The downside of this though, is that when a field’s reporting settings change in one team project in one collection on a server, it can impact the data warehouse experience for everybody else on that server.
The good news is that through our dogfooding efforts on the Pioneer server, we found some issues early enough and made changes to the product before it shipped to avoid some other issues. While we were investigating and fixing these issues, we developed some reports that visualize the information that TFS stores about the health of the data warehouse to track our improvements. Now we’re sharing those reports with you.
Please download them (below), install them, send us any feedback (comments on this blog are fine) and we’ll work on including them in the next official Power Tools.
These reports are useful to TFS administrators, operations/support teams, project administrators & end-users. The reports in this pack display the following kinds of information:
In the download, there is a FAQ document which includes screenshots of what different reports mean and common questions. The contents of the FAQ are also available at Monitoring the TFS Data Warehouse – FAQ.
Download and install this report pack to the Team Foundation Server Reporting Services Instance to monitor warehouse and cube processing status.
For information about how to install this report pack, see Installing Admin Report Pack for TFS.docx that is included in the download.
The pack includes:
Here’s an overview of how the reports look and what questions you can answer with them.
Use this report when you want to answer the following questions:
This table tells you whether the warehouse is currently being processed or when it will be processed next.
The Next Run Time is in local time, and Run time is the duration of the job that is currently running.
This table lists all team project collections and all Data Adapter Jobs and displays how long ago the Warehouse and Cube data were updated. In addition, the table will show the schema conflicts and other Data Adapter errors that caused data to be out of date:
If a conflict occurs when two schemas merge, the table will show the conflict with a link to the sub report that displays details about the blocked fields.
If you click the link that appears under the schema conflict error, a different report appears and shows the fields that are currently active and blocked for the blocked team project collection.
The following table shows blocked fields that have conflicts over all team project collections.
Blocked fields appear before the fields with which they are conflicting. The conflicting areas appear in bold.
The following table shows field changes that are queued behind the blocked changes in the previous illustration.
After you resolve the blocked changes, the queued changes will then be applied to the warehouse.
This report shows all reportable fields in the deployment of Team Foundation Server. Administrators of team projects should review this report before they add a reportable field or change the properties of an existing field. The report helps prevent potential schema-merge conflicts. It lists fields across all collections, including any fields that are blocked.
Found 2 fields that match ‘Found In’
This report shows list of recently completed Cube processing jobs.
I hope that you find these reports useful. Please comment on this post if you have questions or other feedback.
Grant, we are currently running TFS 2008, will these reports translate down?
@Matt - No, these are TFS2010 only. TFS2008 used a different architecture for processing the warehouse (the processing history isn't stored anywhere) and the blocked fields situation doesn't exist, because there's only one server.
Grant seems to be a possible bug in the reports? Any ideas?
@Allen - We're testing a fix to the reports now. This post will be updated once it's validated.
@Allen - the download link has been updated with a fixed report that includes the 'Optimize Databases' job.
@Grant - I downloaded and tested the new version and all looks good. Thanks!
Grant - Thanks for fixing the report and I've downloaded a copy and updated our TFS servers. It's all working now! :) Would love to see additional useful reports being released. ;)
Thanks for the great reports!
Hi Grant, I downloaded the zip file and expanded it but do not see the install doc.
@Carol - I just downloaded it myself and extraced it. There is a file in the AdminReportPack sub directory called "Installing Admin Report Pack for TFS.docx". If you are still having problems, send me an email via blogs.msdn.com/.../contact.aspx and I will send you the doc.
I am trying to run the reports and I am getting the following error when running the Cube Status report:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'dsProcessingStatus'. (rsErrorExecutingCommand)
Invalid object name 'tbl_jobqueue'.
I am running this with the admin account and it should have full access to everything. Can you point me in the right direction to fix this error?
I am getting same error as previous comment, can anyone help me to fix this?
An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset 'dsProcessingStatus'.
Grant - Thanks for the great reports! One question regarding the Reportable Fields report. I would like to add a column for "unused", could I just use report builder to add this parameter?
Did anyone find an answer for the Invalid object name 'tbl_jobqueue' error mentioned above?
I am also getting the 'tbl_jobqueue' error message and have not found a resolution. I have installed the Administrative Report Pack on two different TFS instances with no success.