Why aren’t my TFS reports updating?

The Background

I was working with a client this week to install a fresh TFS 2008 dual-server instance and then add SP1 on top.  They had been having some issues getting the QA instance installed so were a bit gun-shy when it came time to install the Prod instance.  As such they wanted to verify all of the services and settings after before and after the SP1 installation.

One of the pain points they had encountered was around the Analysis Services Cube and Reporting.  We decided to verify the install by making checking the reports, making changes to some work items and then checking the reports again to see the updated information.

To facilitate this process we changed the Data Warehouse’s update interval to 1 minute by following the instructions here (How To: Set the Processing Interval for the Data Warehouse).

The Problem

After making this change we waited a couple of minutes and then ran our first Related Work Items report.  We then added a new Task work item and related it to an existing Scenario work item, waited a couple of minutes and then ran the Related Work Items report.  To our surprise, the new work item wasn’t listed.  We reviewed the report and noticed that the Last Warehouse Update value hadn’t changed between the two report runs.

LastWarehouseUpdate 

The Investigation

I started wondering about caching of the reports so I tested this theory by running the Exit Criteria report.  This one showed a current warehouse update timestamp.

LastWarehouseUpdate2

Ok so there’s definitely some report caching going on here!  I can see that the warehouse is getting updated so I closed everything down and re-opened the Related Work Items report.  Voila!…no change. 

After a bit of head scratching and complaining under my breath I decided to put all my years of debugging experience toward solving this problem.  I said to myself; “Steve…” (I call myself Steve) I said “Steve, what could be caching these reports?”  Luckily I’m quite adept at single-person conversations so I immediately answered myself with “Maybe it’s the SQL Server Reporting Services that’s doing it, stupid!” (I have no patience for ignorance).

The Root Cause

I started up Internet Explorer and browsed to http://{yourTfsServerHere}/reports to view the Reporting Services settings.  At the Home page i clicked on the Site Settings link (upper right corner) to check to see if this are any site-wide caching going on.  Looking over this page you see that there aren’t any settings for caching at this level that don’t require force you to modify the database directly (this article does tell you how to manage Report Server level caching).

I then navigated back Home and then drilled down into my BuildProject reports.  I’m going to see if the reports have some kind of report-level caching going on.  I opened the Related Work Items report.  When it rendered to the page I noticed that it also had the same timestamp as the last run. 

To check the settings of the report I clicked on the Properties tab and then reviewed the options available to me.  The Execution section looked like a good place to start.

Lo and Behold!  The first entry in this section is “Always run this report with the most recent data” and it has 3 options.  The selected (default) option here is to “Cache a temporary copy of the report. Expire copy of report after a number of minutes” with a value of 30.  PAY DIRT!!!

ReportExecutionPropertiesDefault

It looks like the reports default to caching themselves for 30 minutes after the last rendering!

The Fix

To change this behavior i modified the setting to use the “Do not cache temporary copies of this report” entry instead.  Here are the steps:

  1. Open SSRS in your browser at http://yourTfsServer/reports
  2. Click on your Team Project name
  3. Click on the report you want to modify
  4. Click on the report’s Properties tab
  5. Click on the Execution link
  6. Select “Do not cache temporary copies of this report” radio button under the “Always run this report with the most recent data” section.

 ReportExecutionProperties

In Closing

After completing these steps we were began enjoying a quick Change, Save, Review feedback cycle and completed our verification of the TFS installation and SP1 upgrade.  We subsequently changed all of the caching and warehouse update times back to their default values.

The default settings for the warehouse update and caching are Microsoft’s best guesses at appropriate values for most organizations.  If your Analysis and Reporting servers can handle the load and you want “fresher” data for your reports feel free to modify these values.

Settings Recommendation

After reviewing this situation I’ve come to the realization that it is probably a good idea to set the Report Caching timeout to around 50% of the value you have set for the Warehouse Updated value.  This way there will be minimal lag between warehouse updates and expiration of the report cache.

Additional Information / Resources

Report Manager: Execution Properties Page –  http://msdn.microsoft.com/en-us/library/ms178821.aspx

Report Caching in Reporting Services – http://msdn.microsoft.com/en-us/library/ms155927.aspx

Database Journal: Black Belt Administration: Caching Options: Report Session Caching – http://www.databasejournal.com/features/mssql/article.php/3695721

Database Journal: Black Belt Administration: Report Execution Caching I: SQL Server Management Studio Perspective – http://www.databasejournal.com/features/mssql/article.php/3699546

Database Journal: Black Belt Administration: Report Execution Caching II: Report Manager Perspective – http://www.databasejournal.com/features/mssql/article.php/10894_3701041_1