Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

November, 2008

  • Brian Smith's Microsoft Project Support Blog

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

    • 11 Comments

    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!)

    Technorati Tags:
  • Brian Smith's Microsoft Project Support Blog

    Project Server 2007: Where did my Web Part go?

    • 10 Comments

    We had a recent customer incident where they could not drill in to projects from Project Center – they just saw a blank section of the page where the drill down should be on the ProjectDrillDown.aspx page.  The web part was gone.  Not sure what had happened – it could have been removed from the page by use of the Web Part Page Maintenance page, which might have been displayed after an error – or it could have been removed accidentally (but not easily – as you will see later…).  Part way through the support incident they had the web part back – but all the drill downs went to the same project.  What was going on?  Read on…

    Blank Page

    With most cases like this, and the My Tasks page is a good example, you just add the Web Part back and all is good.  However, this page is different as it does not normally allow any layout changes – so web parts cannot be added (or removed).  If you go to Site Actions, Edit Page, there is no “Add a Web Part” option.  The first challenge here then was to get to a position where we could add a web part back again – and this involved editing the aspx page (using Notepad or your favorite text editor) – but first of course you make a backup copy!  The file can be found in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\SiteTemplates\PWA by default, and the change is simple – at the end of the aspx page you will find the text AllowLayoutChange="false" and you need to change this to AllowLayoutChange="true".  No IISRESET needed – just save and refresh the page and you will be able to add web parts.

    The next challenge was that the list of Web Parts we can add had a Project Details Web Part – and this was added – but the behavior wasn’t as expected.

    WebPartsList

    Whichever project was chosen from the Project Center we saw the same project in the details page – and in fact this was the project that had to be chosen in a dropdown on the web part when it was added.  In fact this is expected behavior as the Web Part added is really intended to be used on a page to show a specific project and does not have any links to the Project Center.  If the Project Details web part is not the right one then how do we fix our page?

    The way we resolved this issue was to go to a working instance of the PWA and the ProjectDrillDown.aspx page and using either Site Actions, Edit Page, or simply the unlabelled drop down in the top right of the Web Part, then choosing Export we can save this working Web Part as a .webpart file. 

    To Import you need to use the link at the foot of the “Add Web Parts” page.

    Link to WebPart Gallery

    Then from the next page you can use the Import option (on the drop down that initially says “Browse”) where you can browse to the saved .webpart file, Upload the part and then Import to the page.

    After Upload

    And you have a fixed ProjectDrillDown page which drills down to the right projects!  Of course the final step is to edit the page back to AllowLayoutChange="false".

    We haven’t seen this issue frequently so the loss of the web part isn’t a common scenario – but thought it worth giving details of the repair just in case others run into it. 

    This isn’t intended to be a lesson in customizing pages and if any issues arise due to other customizations achieved by changing the false to true then from support we would request you replace the original page to see if the issue was related to your customizations.

    Technorati Tags:
  • Brian Smith's Microsoft Project Support Blog

    Project Server 2007: TechNet Document now live for using SQL Server 2008

    • 1 Comments

    ****Update**** – The December 2008 release of the Feature pack for SQL Server 2005 at http://www.microsoft.com/downloads/details.aspx?FamilyID=536FD7D5-013F-49BC-9FC7-77DEDE4BB075&displaylang=en means that loading the Management Objects Collection from this release will avoid the workaround described in the TechNet article. 

    The writers have done a great job on revising the 2005 document to address 2008 – thanks Efren.  The document is here - http://technet.microsoft.com/en-us/library/dd285466.aspx.  This includes some of the gotchas mentioned in my recent blog as well as the error messages you might see.  There is still an issue with the Management Objects needed on the application server for connecting to 2008 which will be resolved by SQL Server 2005 SP3, and the next feature pack refresh – the current workaround can be found at the foot of the TechNet article.

    Another change is that the 2 methods of configuring a repository have been swapped round – so the SQL Server repository is now Method 1 – which reflects the fact that this method should really be the preferred one.  Hope this switch doesn’t confuse too many people (particularly as it was my idea).

  • Brian Smith's Microsoft Project Support Blog

    Now fixed: COMException on x64 platforms when automating Office clients via the Primary Interop Assembly (PIA)

    • 1 Comments

    I had the original posting early in the year, and having got another question on it I checked and the hotfix was released back in April to resolve this issue http://support.microsoft.com/kb/950136/en-us.  This hotfix is for the OS and not Project.  It also resolves the problem for other Office applications such as Word – which has some big object.  So if you are running x64 Vista or Windows Server 2003 and want to access large objects this is for you.

    SYMPTOMS

    On a computer that is running the 64-bit version of Windows Vista or the 64-bit version Windows Server 2003, no more than 1,024 methods can be present in one COM interface.
    This limitation makes some applications that have more than 1,024 methods, such as Microsoft Project (WinProj.exe), unable to contain more methods.

    Hotfix information

    A hotfix is available to extend the maximum number of methods on a COM interface to 2,048.

    Just to help the search engines – the original error was:

    Error HRESULT E_FAIL has been returned from a call to a COM component. System.Collections.ListDictionaryInternal.

Page 1 of 1 (4 items)