Troubleshooting the Data Warehouse

To confirm that data has stopped flowing

1. Make one or more of the following changes:

• Check in a changed file to source control.
• Update a work item.
• Publish a test result.

2. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
3. In the Server Type list, in the Connect to Server dialog box, select Database Engine, and then click Connect.
4. In Microsoft SQL Server Management Studio, click New Query.
5. Confirm the change appears in the appropriate tables by running the following query:
   select * from [work item] where id = [select max[id] from [work item] ]

If the change does not appear, the data has stopped flowing.

To identify the failed adapter

1. On the Team Foundation Server application-tier computer, click Start, point to Run, and then type inetmgr in the Open box.
2. In the left pane of Internet Information Services Manager, click the Team Foundation Server node.
3. On the Action menu, click Open.
4. Click the Web.Config file, and then on the File menu, click Edit.
5. Find the line <add name=”General” value=”0″ />, and replace “0” with “3”. This change enables debug messages.
6. By using a program that listens to system trace events, start tracing on the application tier. Many freeware programs provide tracing and debugging capabilities using the .NET tracing capabilities, or custom applications can be built to provide this functionality. For more information about how to use tracing and debugging capabilities, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconTraceListeners.asp.
7. On the Capture menu, select all capture commands. Keep this program open to see all DebugView messages.
8. In the left pane of Internet Information Services Manager, expand the Warehouse node, and then click the v1.0 node.
9. In the right pane, click warehousecontrolled.asmx.
10. On the Action menu, click Browse.
11. In the ControllerService, click Run, and then click Invoke.
12. By using your trace monitoring application from step 6, review the log for trace exceptions that indicate which adapter is not functioning correctly.

To turn off Internet Explorer friendly error handling

1. On the application tier, start Internet Explorer.
2. On the Tools menu, click Internet Options.
3. In the Internet Options dialog box, click the Advanced tab.
4. Clear the check box Show friendly HTTP error messages.

To confirm the TFSServerScheduler windows service is running

1. On the data tier, click Start, point to Administrative Tools, and then click Services.
2. In the Services window, scroll to the TFSServerScheduler, and confirm that the Status column says Started.

If the service is not started, right-click TFSServerScheduler, and then click Start.

3. Determine the last time the scheduler called the warehouse to process by browsing to %ProgramFiles%Microsoft Team Foundation Server 2005TfsServerScheduler.

The xml file in that directory records the last time warehouse processing was tried.

To confirm the processing pulled data into the warehouse

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. In the Server Type list, in the Connect to Server dialog box, select Database Engine, and then click Connect.
3. In Microsoft SQL Server Management Studio, click New Query.
4. Determine the amount of data that made it into the data cube by running the following query:

use TfsWarehouse

select Setting as [Last Cube Processing] from _WarehouseConfig where ID = ‘LastProcessedTime’

— total revisions in the relational warehouse

use TfsWarehouse

select top 1 LastUpdatedTime as [LastVCWHWrite] from dbo.[Code Churn] with (nolock) order by LastUpdatedTime desc

select top 1 LastUpdatedTime as [LastWITWHWrite] from dbo.[Work Item History] with (nolock) order by LastUpdatedTime desc

use TfsWarehouse

select count(*) as [WH Revisions] from [dbo].[Work Item History] with (nolock)

where [Record Count] <> -1

use TfsWorkitemTracking

select count(*) as [WIT Revisions] from [dbo].[WorkitemsLatestAndWere] with (nolock)

use TfsWarehouse

select max ([Changeset ID]) as [WH Changeset] from [dbo].Changeset with (nolock)

where DimensionMemberActive = 1

use TfsVersionControl

select max(ChangeSetId)  as [VC Changeset] from tbl_Changeset with (nolock)

— identities

use TfsWarehouse

select Property_Value as [Warehouse Identity Id] from _PropertyBag

where Property_Key = ‘CSS Identity Sequence Id’

use TfsIntegration

select max(sequence_Id) as [Integration Identity Id] from tbl_security_identity_cache

— structure

use TfsWarehouse

select Property_Value as [Warehouse Structure Id] from _PropertyBag

where Property_Key = ‘CSS Structure Sequence Id’

use TfsIntegration

select max(sequence_Id) as [Integration Structure Id] from tbl_nodes with (nolock)

If no data is present, or data is severely latent, either the scheduler is not signaling processing or there is a problem in the warehouse aggregating data.

To manually process the warehouse

1. Log on to the application tier.
2. Open http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx.
3. Click GetWarehouseStatus, and then click Invoke.

Wait for the service to idle; it should return ProcessingAdapters (schema modifications), ProcessingOlap (schema modifications), ProcessingAdapters (pulling data), ProcessingOlap (processing the cube), and then idle.

4. Open http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx.
5. Click Run and then click Invoke.

This starts the processing of the warehouse. The service returns True or False whether it has started processing (asynchronously).

6. You can repeat step 3 to monitor the processing.
7. To verify, you can repeat step 2 to validate data was moved over and then if there is a problem, see the “To gather error data” procedures in the following section for the time range of the processing.

To open the reports

1. In Team Explorer, expand the Reports node, and browse the individual team reports.

Each report has the time the warehouse last processed, expressed in the following form:
Report Generated: yyyy/mm/dd hh:mm:ss by domainuser; Last Warehouse Update: yyyy/mm/dd hh:mm:ss

The data should be current and less than an hour old.

2. If the data is more than an hour old, disable caching for the report:

   a.      In Internet Explorer, open http://<server>/Reports/.
   b.      In Report Manager, click the team project name, and then click the report.
   c.      Click the Properties tab and then click Execution.
   d.      Select Do not cache temporary copies of this report and then click Apply.
   e.      Click the View tab, and view the report.

To gather error data

1. Review the application event log and see whether there are any errors, as shown as a red X, from the source ‘Warehouse’. The error and error stack in that event log can help identify the problem.
2. If all the steps are leading to errors, gather a trace to help the support specialist or product team member diagnose the issue.
3. Use a tool such as DBMON to capture debug output.
4. On the Team Foundation Server application-tier computer, click Start, point to Run, and then type inetmgr in the Open box.
5. In the left pane of Internet Information Services Manager, click the Team Foundation Server node.
6. On the Action menu, click Open.
7. Click the Web.Config file, and then on the File menu, click Edit.
8. Find the line <add name=”General” value=”0″ />, and replace “0” with “4”. This change enables debug messages.