Use of “SetupWarehouse.exe –rebuild…” can cause data loss in Team Foundation Server 2008

Ladislau brings us some important information today about rebuilding the TFS warehouse…


Each component of Team Foundation Server stores data in operational stores. Every hour or every time a new team project is created the warehouse web service is invoked which uses adapters to upload new data from the op stores into the TfsWarehouse relational database and from there into an OLAP cube. TFS reports are displaying data from the OLAP cube. 

When customers call us with issues like the outdated report data or their reports fail with an error message, we usually help them to rebuild the warehouse as that tends to resolve most of the warehouse related issues. So how do we rebuild the warehouse?

There are three supported combinations of Setupwarehouse.exe

1. Setupwarehouse.exe –o –s

2. Setupwarehouse.exe –rebuild –mturl

3. Setupwarehouse.exe /setup /install –n –s

No matter how it is done, a successful rebuild of the Warehouse relies on the operational stores retaining their historical information. Team Build, however, is an exception. During a conversation with the product group it came to our attention that rebuilding the warehouse using 2) and 3) can cause some data loss in Team Foundation Server 2008 RTM and Team Foundation Server 2008 SP1. Needless to say, this information was quite alarming for engineers inside CSS as one of the troubleshooting techniques for warehouse issues is to rebuild the warehouse. We were worried that we have to explain to our customers that they are missing warehouse data due to an action plan provided by CSS.

Did you get worried while reading the previous paragraph?  It is not as bad as it sounds and I will try to explain why.

During Team Build, the items specified in the “SolutionToBuild” item group are built using msbuild for configurations specified in the “ConfigurationToBuild” item group. Based on the information from these two items groups, the built-in build logger records compilation summary and configuration summary. Apart from other information, this information is stored in the dbo.tbl_BuildInformation table in a tree structure. The root nodes of each tree are the topmost projects or solutions which were included in the “SolutionToBuild” item group.

When a build completes the tree is walked and for each configuration we sum up the number compilation errors/warnings and static analysis errors/warnings. In case the number of compilation errors/warnings and  static analysis errors/warnings is 0, we mark this node as unnecessary using the "Required" column from the table. It is very important to understand that this does not happen for root nodes. We never mark root nodes as unnecessary.

During warehouse sync, the Build adapter uploads the build information to the TfsWarehouse relational database. When the upload of data is completed the tbl_ServerStatus table is updated with a watermark of the last uploaded build. Each night there is a SQL job (TfsBuild Team Build Administration Job) which runs and based on the information from the tbl_ServerStatus table, removes all unnecessary nodes from builds that have already been uploaded to the TfsWarehouse relational database.

That means that if  you rebuild the warehouse, this information will be missing from the build op store hence it will not be uploaded by the adapter in the TfsWarehouse relational database.

The obvious question is:  Will this rebuilding the warehouse affect the data from the build reports?

The short answer is no. The build related reports that come with Team Foundation Server display compilation summary information at the root node level. As a result, even though rebuilding the warehouse removes compilation summaries for child nodes that have 0 errors/warnings and static analysis errors/warnings, it will not affect the data displayed in the reports.

In case you are still uncomfortable with the fact that data is removed from the build  op store, you can manually remove the “Delete Unnecessary Build Information” step from the “TfsBuild Team Build Administration Job” job. By doing that, the information marked as unnecessary will not be removed from the build op store. This is not retro-active though, and will not recover deleted data. It will only apply to build information in the SQL data engine going forward from time of change.

Comments (3)

  1. Brett Keown is at it again… In TFS Developer Support, we lovingly call this process “The Big One”. 

  2. edblankenship says:

    There is one other thing that is missing:  the fact that the build ever occurred if it was deleted manually or through retention policies that are setup for a build definition.

    For example, you have retention policies setup and you want to see you pass/fail rate for the builds for the year to date.  If you rebuild the warehouse in October, you will only get the builds that still remain in the operational databases at the time of the warehouse rebuild.  It won’t include any of the deleted builds.

    Causes big problems when you want to know things like "How many builds did we do for the year by build definition?  What was the pass/fail rate? etc."

    Just keep that in mind and save off Excel workbooks with the metrics you want to really keep.

  3. Before setting up my own blog, I have been posting on the Developer Support Team Foundation Server blog.