Project Server Blogs

Archive for November, 2008

— Next Page »

Microsoft SQL Server 2008 Upgrade Technical Reference Guide is Live!

This 490-page white paper covers the essential phases and steps to upgrade existing instances of SQL Server 2000 and 2005 to SQL Server 2008 by using best practices. These include preparation tasks, upgrade tasks, and post-upgrade tasks. It is intended to be a supplement to SQL Server 2008 Books Online.

The categories in the following list correspond to chapters in the white paper.

• General Upgrade Issues
• Relational Engine Components
• Business Intelligence Components
• Other Microsoft Applications and Platforms

Other related links:

• SQL Server 2008 Upgrade Technical Reference Guide
• Resources for Upgrading to SQL Server 2008
• UE Team blog
• How to configure Project Server 2007 Cube Building Service with SQL Server 2008 Analysis Services

Popularity: 1% [?]

Running SQL Server 2008 in Hyper-V Environment

The SQL has just released the following article: Running SQL Server 2008 in a Hyper-V Environment, Best Practices and Performance Considerations

Summary:

Hyper-V in Windows Server 2008 is a powerful virtualization technology that can be used by corporate IT to consolidate under-utilized servers, lowering TCO and maintaining or improving Quality of Service. Through a series of test scenarios that are representative of SQL Server application fundamentals, this document provides best practice recommendations on running SQL Server in Windows Hyper-V environment.

Related links:

• SharePoint Server performance and capacity requirements for Hyper-V
• Evaluating Application Performance on Hyper-V
• Project Server Hyper-V and Active Directory Synchronization Documentation
• Project Server Hyper-V Virtualization Support and Licensing

Popularity: 1% [?]

Project Server 2007: Why is my Archive DB so big?

In the August Cumulative Update for Project Server 2007 there was a fix for an issue with archiving projects (the Administrative Backup feature).  Some customers had noticed that the archive database was bigger than expected in relation to the retention policy they had set, and the size of their draft database.  In normal terms you might expect that with a project retention policy of 3 your archive database would be about 3 times larger than your draft database – all things being equal (which of course they never are – but a reasonable estimate).  So when it was 10+ times bigger this didn’t look right.  The description of the fixed bug in the KB article was:

• When a project is deleted from the archived database, the related entities are not deleted if a backup retention policy that includes more than one version is enabled.

The term “deleted from the archive database” refers to the automatic action taken when you have a retention policy of, for example, 3 and then the administrative backup saves the 4th iteration of the project.  The first iteration of this project that was archived gets flushed out of archive database.  Before this fix only the entry in the MSP_PROJECTS table was removed – and the remaining entities referred to in the bug description are contained in the following tables in the Archive database:

MSP_ASSN_CUSTOM_FIELD_VALUES
MSP_PROJECT_FRONT_END
MSP_TASKS
MSP_TASK_BASELINES
MSP_PROJECT_RESOURCES
MSP_PROJECT_RESOURCE_BASELINES
MSP_ASSIGNMENTS
MSP_ASSIGNMENT_BASELINES
MSP_PROJECT_CALENDARS
MSP_PROJECT_CALENDARS
MSP_PROJECT_LOOKUP_TABLES
MSP_PROJECT_LOOKUP_TABLE_STRUCTURES
MSP_PROJECT_LOOKUP_TABLE_MASKS
MSP_PROJECT_CUSTOM_FIELDS
MSP_PROJECT_CUSTOM_FIELDS
MSP_TASK_CUSTOM_FIELD_VALUES
MSP_PROJ_RES_CUSTOM_FIELD_VALUES

After the fix all rows in all the tables for any newly deleted project version are correctly removed – but the old orphaned records are still there.

Assuming you have archived more versions than your retention policy (pre August CU) then you can expect there to be records in each (or most) of these tables that relate to project versions (by GUID) that no longer exist in the MSP_PROJECTS table.

The extent of the problem will depend on your use of this feature before the August CU, size of your projects in terms of tasks, assignments and the other entities in these tables.  If you have made extensive use of this feature with very large projects then this could lead to your archive database being many times bigger than it needs to be.  So how to recover this wasted space?  Not an easy answer.  The delete query that will get rid of the orphaned records is very straightforward – but before you rush to SQL Management Studio to delete them a few words of warning regarding tempdb, transaction logs and estimating the size of the problem.

The basic query required to delete from each of the tables will look like this:

USE ProjectServer_Archive

Delete from MSP_ASSN_CUSTOM_FIELD_VALUES
where NOT EXISTS (select * from MSP_PROJECTS
where MSP_PROJECTS.PROJ_UID = MSP_ASSN_CUSTOM_FIELD_VALUES.PROJ_UID)

(repeat for each table listed above)

We are looking for any rows in the table MSP_ASSN_CUSTOM_FIELD_VALUES that do not have corresponding records (matching PROJ_UIDs) in the MSP_PROJECTS table – then we delete them.  Regardless of your recovery model this will get recorded in your database transaction logs – and if you have millions of rows to delete then this will take a while and use GB of space – increasing the size of your transactions logs and also potentially your tempdb. 

WARNING! The following details and guidelines are supplied “as-is” and if you are not confident with SQL Server queries and administration then either work with your database administrator or open a support incident to get help from Microsoft.  Any large scale deletions are best carried out outside normal hours, and also at a time that does not conflict with backups.  There are certainly ways to minimize the impact – and you don’t need to clean everything up at once – and indeed you don’t need to clean it up at all if you are happy to waste a little disk space.

First thing to understand then is how many orphaned projects do I have out in my archive db – and how many projects that should be there?  The first answer can be obtained by using the query:

Select count (DISTINCT PROJ_UID) from MSP_TASKS
where NOT EXISTS (select * from MSP_PROJECTS
where MSP_PROJECTS.PROJ_UID = MSP_TASKS.PROJ_UID)

This tells us how many distinct project UIDs exist in the MSP_TASKS table that don’t correspond to “real” projects. 

And for the total real projects this is simply:

Select count (DISTINCT PROJ_UID) from MSP_PROJECTS

With these two numbers you can get an idea how much excess baggage your archive db is carrying – then we can think around the different possibilities for cleaning this up.  I played around with one of my test databases where I had around 2000 projects and 400 orphans.  The archive database was about 30GB, but probably should have been 25GB.  Not too extreme – you may have significantly more or fewer orphans and the clean up approach for each scenario may be different.

If you only have a small number of orphans – both in terms of % and magnitude then the simple delete query mentioned above may well cut it for you.  Just to give you an idea of how this works, the full deletion from all the tables on my server probably got rid of around 3 million rows across the 17 tables in about 9 hours.  My transaction log (with recovery mode set to simple) grew to 20GB and my tempd to 800MB.  The memory usage (working set) of this instance of SQL Server went to 3GB.  During this time my server (x64, Dual Proc, 4GB RAM) was unusable for anything else.  If your server does other work or has other SQL instances then this may be a good time to limit the resources so it does not slow your server down too much.

If you have a small % but it is still a large number then the deletion may be better handled using a different approach.  The reason the transaction log grows even with simple mode for recovery is that this is treated as a single transaction.  Once it is completed the used space in the transaction log is negligible and the data file can be shrunk down.

An approach that overcomes this growth in both transaction log and tempdb, but is a little slower is to use a loop to delete in a large number of smaller transactions.  This example was found at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62356 (thanks Kristen) but there are many good examples out there.

DECLARE @intRowCount INT

DECLARE @intErrNo INT

DECLARE @intRowsToDelete INT

DECLARE @dtLoop DATETIME

SELECT    @intRowCount = 1,    – Force first iteration

    @intErrNo = 0

SELECT    @intRowsToDelete = COUNT(*)    – Number of rows to be deleted

FROM    MSP_ASSN_CUSTOM_FIELD_VALUES

WHERE    NOT EXISTS (select * from MSP_PROJECTS 

 where MSP_PROJECTS.PROJ_UID = MSP_ASSN_CUSTOM_FIELD_VALUES.PROJ_UID)

WHILE @intRowCount > 0 AND @intErrNo = 0

BEGIN

    SELECT    @dtLoop = GetDate()

    SET ROWCOUNT 10000    – number of delete rows / iteration

    DELETE    

    FROM    MSP_ASSN_CUSTOM_FIELD_VALUES

    WHERE    NOT EXISTS (select * from MSP_PROJECTS 

 where MSP_ASSN_CUSTOM_FIELD_VALUES.PROJ_UID = MSP_PROJECTS.PROJ_UID)

    SELECT    @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

    SET ROWCOUNT 0    – Reset batch size to "all"

    SELECT    @intRowsToDelete = @intRowsToDelete - @intRowCount

– Debugging usage only:

PRINT ‘Deleted: ‘ + CONVERT(varchar(20), @intRowCount) 

    + ‘, Elapsed: ‘ + CONVERT(varchar(20), DATEDIFF(Second, @dtLoop, GetDate())) + ‘ seconds,’

    + ‘ remaining to delete=’ + CONVERT(varchar(20), @intRowsToDelete)

    – WAITFOR DELAY ‘000:00:05′    — 5 seconds for other users to gain access

END

Again, this example just shows the code for a single one of the 17 tables.  I have commented out the “WAITFOR” section – but if you are using this type of code in a scenario where you need to let others have some db time during the process then worth using this option.

In my sample database this approach limited transaction log usage (simple recovery mode) to 600MB and tempdb to 15MB.  Memory still peaked quite high – so you might want to set some limits.  Also it took about 50% longer to run – at around 14 hours.

If your orphans make up the bulk of your table and the data you need to keep is just a small percentage then another approach might be to copy out the stuff you need to either a temporary table or file, then truncate the table and finally copy back in the saved good stuff.  There are still some gotchas here, as although bulk inserts don’t generally generate a lot of log usage – if the table being inserted into have indexes then the re-creation gets logged – even in simple or bulk insert recovery models.  Some examples of the code you might use here, again just dealing with a single table. 

First lets look at a simple temporary table:

use Test_Archive

select* into MSP_ASSN_CUSTOM_FIELD_VALUES_Backup from MSP_ASSN_CUSTOM_FIELD_VALUES

where EXISTS (select * from MSP_PROJECTS 

where MSP_PROJECTS.PROJ_UID = MSP_ASSN_CUSTOM_FIELD_VALUES.PROJ_UID)

truncate TABLE MSP_ASSN_CUSTOM_FIELD_VALUES

insert into MSP_ASSN_CUSTOM_FIELD_VALUES 

Select * from MSP_ASSN_CUSTOM_FIELD_VALUES_Backup

This is one of the fastest approaches, if you have the disk space, as it uses extra space in the archive db for the temporary tables, then uses a whole load of transaction log and tempdb for the insert back into the table.  In my sample db on the largest table in terms of rows, MSP_ASSN_CUSTOM_FIELD_VALUES, it took 9 minutes to push the data out, the truncate was instant (and isn’t logged – so not much use of transaction logs up to here) then the insert took 30 minutes and used 19GB of transaction log and 1.7GB of tempdb!

The final approach was to try a similar process, but using bcp and going via a file.  My output was based on a query to get the rows I wanted to keep so the bcp statement run from a command prompt was:

bcp "select * from Test_Archive.dbo.MSP_ASSN_CUSTOM_FIELD_VALUES where EXISTS (select * from Test_Archive.dbo.MSP_PROJECTS where Test_Archive.dbo.MSP_PROJECTS.PROJ_UID = Test_Archive.dbo.MSP_ASSN_CUSTOM_FIELD_VALUES.PROJ_UID)" queryout "MSP_ASSN_CUSTOM_FIELD_VALUES.dat" -T –c

This exported my 8 million rows from this table in around 9 minutes, creating a 1.7GB file. I then truncated the table and used a single batch bcp to import:

bcp Test_Archive.dbo.MSP_ASSN_CUSTOM_FIELD_VALUES in MSP_ASSN_CUSTOM_FIELD_VALUES.dat -T –c

This took around an hour, used 19GB transaction log and 1.7GB tempdb – so similar (but slower) than the table insert.  Another approach that limits the tempdb usage and transaction log usage is to use the –b flag on the bcp to send it to the server in smaller batches.  I tried:

bcp Test_Archive.dbo.MSP_ASSN_CUSTOM_FIELD_VALUES in MSP_ASSN_CUSTOM_FIELD_VALUES.dat -T –c –b1000

and as I write this blog it has used no tempdb, (none over the default 8MB anyway) just 800MB of transaction log – but it has been running for 2 days so far and is just over half way through.  I’ll post an update if and when it finishes!

So there is no simple answer – and even more options.  You could remove indexes while importing, and then add them back later – though you’d take a hit for the index rebuild at some time.  Re-organizing the data before import could work faster in terms of indexing.  One other possibility if you don’t have anything you want to keep is to truncate all of the above tables from the archive db – including the MSP_PROJECTS one.  Or even completely re-provision your PWA using backups of your draft, published and reporting databases – but a blank archive.  You’d need some support help on this one.

Finally whichever option you choose you will then want to shrink your files and database to recover space.  This too might take some time –depending on which method you used and how much data/space is in the database.

I hope this helps you to understand if the nature of this issue and what if anything you might need to do to regain your lost space.  Certainly food for thought.  And on the subject of food – Happy Thanksgiving to my US readers who catch this posting before their holiday break (which hopefully will not be too many of you – at 4:30PM PST on 11/26!)

Popularity: 3% [?]

Thankful

Given the season I have started thinking about what I’m thankful for. Here is my list of a few things I am thankful for this year:

1. My wife and my children. All the normal clichés apply of course but they are clichés. I would not be where I am or who I am without them.
2. The health of my grandparents. At 90 and 87 they are going strong.
3. The almost unbelievable luck of getting to work for Microsoft getting paid to do something I love to do and am good at doing. At least once a month I think about how lucky I am to be here.
4. My team, The MCS EPM Global Practice. Scary smart and dedicated to making sure that customers are doing their best work around project and portfolio management.
5. Multiple Level Undo. I get goose bumps just writing the words.
6. The Election
7. The Zune. All you can eat music.
8. The Amazon Kindle. Books books and more books.
9. Windows Server 2008 running on a Lenovo T61p with 4 gigs of RAM. Wow!
10. The promise of the future

Popularity: 1% [?]

Quick Trick — Align Task Rows with Gantt Bars More Easily

Popularity: 4% [?]

Inside SharePoint: Enterprise Project Management with SharePoint

Check out the following article from TechNet Magazine (December 2008 edition) from Pav Cherny on how to deploy Project Server 2007 on top on Windows Server 2008 and SQL 2005: Inside SharePoint Enterprise Project Management with SharePoint.

Sections include:

• Project Server Architecture
• SharePoint Integration
• Server Farm Deployments
• Welcome to IIS 7
• Session Database Permissions
• Shared Services Permissions
• Windows Firewall
• MOPS Services and Services Accounts
• Analysis Services Integration

Popularity: 1% [?]

Enterprise Project Management - The Shot Heard Around the World

Popularity: 1% [?]

Project Server OLAP Views Errors

Just a warning about special characters. Don’t use them if you want your Project Server Analysis views to work correctly:

Avoid all of these:

Data source
: / \ * | ? ” () [] {} <>
Level
. , ; ‘ ` : / \ * | ? ” & % $ ! - + = [] {}
Dimension
, ; ‘ ` : / \ * | ? ” & % $ ! - + = () [] {}
All other objects
. , ; ‘ ` : / \ * | ? ” & % $ ! - + = () [] {}

Popularity: 1% [?]

Back to basics: Printing your project

Okay, so printing in Microsoft Office Project 2007 might be a little more complex than you’re used to. We’ll give you that. Need some help figuring out how to get your project data printed and looking professional? Read on.

Printing a view

The first step in printing a view is to set up the view itself so that everything you want to print is displayed appropriately. This may involve things like showing or hiding columns (video), changing row height and wrapping text, or changing column titles in sheet views. If you want to print multiple projects in a single view, you might try printing a consolidated project, which enables you to sort, filter, or group the tasks and resources across multiple projects, all in one printable view.

Once you have the view set up the way you’d like it to print, the next step is to adjust your printing options. If you are printing a Calendar view, you’ll need to specify the number of months or weeks you want to print. When printing a view, you can choose to print all notes in the project, or even add a header, footer, or legend.

With your view and printing options all set up, you’re ready to print a view. You can even print your project plan to a PDF file, if you’d rather work with a soft copy.

Printing reports

Another option for printing your project data is to generate reports and print those. You can create and print a basic report, a custom basic report, or a visual report.

What’s the difference? Basic reports and custom basic reports are generated within Project, and offer a limited reporting experience. Visual reports are generated in Microsoft Office Excel 2007/2003, and Microsoft Office Visio Professional 2007, and use PivotTables and PivotDiagrams to provide a rich, dynamic reporting environment. With this flexibility, visual reports provide a more agile reporting solution than basic reports.

Visual reports are printed through Excel and Visio. For more information on printing in these applications, see the following Help areas:

· Saving and printing in Excel 2007

· Printing in Visio 2007

Having trouble?

With so many printing options, it’s understandable that you might run into trouble. The following articles may help:

· What I see on the screen is not what prints

· It takes too long to print my project

· Gridlines don’t print

· Columns don’t print right

Popularity: 1% [?]

The world’s greatest SharePoint admin book is here

Just in time for the holiday season the book is available.

WSS MVP Todd Klindt, Microsoft Enterprise genius Steve Caravajal, and yours truly came together to write this real world, everything you need to know SharePoint administrators guide to the universe. Gets yours today.

And just for fun I will give away a free copy to the first person to fill out the help desk request form on my company website with a link to the post.

What the heck I will also give away another copy to the first person who will post a plug for the book on their blog. Just put a comment on this blog with a trackback.

Thanks

VIA Shane Young – SharePoint Consulting

Popularity: 1% [?]

— Next Page »