Project Server Help, FAQs, Blogs, and HowTos
This is The Header Then

Slow or Failing Cube Builds and Very Large TempDB

posted December 29th, 2007 by Stephen Sanderlin
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5 out of 5)
Loading ... Loading ...

At my present client, prior to the release of Project Server 2007 SP1, we obtained and deployed the hotfix rollup described in KB939594 to resolve some of the issues present in the product prior to the release of SP1.

Unfortunately, after deployment of this hotfix we discovered that when building the cube it would take significantly longer than RTM to build. Specifically:

  • Cubes built with earliest start and latest finish would take an hour and a half or more to build
  • Cubes built with a timeframe of 36 months forward and 13 months back 7+ hours to build and would cause the TempDB to get HUGE (in excess of 200GB)

When running the Cube Build without a date range, the cube would build in an hour or two — but as soon as you introduced a date range, the cube build would jump to 7+ hours, if it was even successful at all. More often than not, however, it would simply grow the TempDB to around 250GB, filling up the disks that the SQL databases were stored on and fail.

At first, we were thinking the problem was perhaps related to data corruption. We had been migrating the client’s projects from their existing Project Server 2003 instance into their new Project Server 2007 instance, and this kind of work always carries the risk of introducing corruption into the system. This client’s database isn’t huge… we’re talking maybe 500 projects with a few hundred tasks in each… longest project probably spans 5 years. As a test, we attempted a cube build with a date range of plus one day and minus one day. This build failed as well, giving us hope that corruption may be the problem. To eliminate this as a possibility, we deleted all of the projects with assignments during this date range from the Published database and rebuilt the cube. The first build was successful, but the second build gave us an unexpected error:

Your permissions on the server computer do not allow you to administer this Analysis server

Further builds also returned the same error. We also attempted using the IP address of the Analysis Services system and changing the OLAP database name, to no avail.

By following the troubleshooting steps for this error that I’ve previously posted, we quickly determined that this error was not accurate and was simply a “default” error. From the data gathered by attaching the SQL Server Profiler to both Analysis Services and SQL Server during a cube build (discussed in the same article), I determined that the query being used to generate the Assignment_Timephased partition of the OLAP Cube was the problem in both build scenarios.

It was at this point that we contacted Premier Support. In working with Premier (and eventually the working with a multiple person team which included Brian Smith), we discovered that this was an issue introduced by a hotfix that was rolled up into KB939594. Unfortunately, this hotfix was also included in SP1. In actuality, this is an issue with SQL Server 2005 that the hotfix simply triggered.

As a point of comparison, when compared to RTM, deployments running either SP1, KB939594, or the specific hotfix in question experienced cube build times ten times longer than builds on RTM using the same data. The TempDB growth we saw was also part of this issue.

Brian Smith has written an article discussing our case, the root problem, and the workaround here. His article is pretty comprehensive and rather than me repost the same information, I will direct you to his article. If you have installed SP1 and are experiencing this issue, I strongly suggest you take a look.

Stephen Sanderlin is Vice President of Technology for MSProjectExperts and a Microsoft Project MVP. His earlier writings on Project Management and Microsoft Project can be read at EPMFAQ.
He is actively posting new content at ProjectServerHelp.

Popularity: 29%

Discuss this post on the EPMFAQ Blog Posts Forum.

Related Posts

« Previous Entry Next Entry »

Please discuss this post on the EPMFAQ Blog Posts Forum.

One Response to “Slow or Failing Cube Builds and Very Large TempDB”

  1. […] Slow or Failing Cube Builds and Very Large TempDB […]

The opinions expressed on this site by the authors and those providing comments are theirs alone, and do not reflect the opinions of the respective employers of the authors or any employee thereof. Our respective employers are not responsible for the accuracy of any of the information supplied by on this site.

Terms of Use | Privacy Policy | Publishers

All content on this website is covered by the
Creative Commons Attribution-Share Alike 3.0 United States License.
Theme ©2007 The Heckerped WordPress Theme created by JTk of Doc5 under the Creative Commons Attribution License.