Paul Liebrand's Weblog

If you have worked with SharePoint 2010 and created a User Profile Service Application (UPA) you may have noticed that your SyncDB is growing larger each and every day. Search around the Internet, you will find posts like this:

http://social.technet.microsoft.com/Forums/en/sharepoint2010setup/thread/70df46de-6dc3-4b2c-8cc6-70b3ff3eb3f4

Other people are definitely having this problem and there are no solutions on how to solve this problem.

I reviewed the Database Types and Descriptions (http://technet.microsoft.com/en-us/library/cc678868.aspx) article on TechNet and it states the following for general size and growth factors:

“Medium to large. Growth factors include the number of users and groups, and the ratio of users to groups.”

Nowhere in this article does it state the database will grow forever with no cleanup process so plan accordingly.

I opened a support ticket with Microsoft on this issue and after a few weeks of troubleshooting I got the following response:

“…The growing of the Sync DB after each sync is expected behavior in SharePoint. My recommendation would be to once a month, delete your UPA (keeping the Social and Profile DB) and recreate the UPA with a new Sync DB thus not allowing the sync DB to grow too large…”

I am sorry – this is not good enough.  I have escalated the issue to the SharePoint product team to get some better clarity or even a possible solution. There appears to be an oversight on the product teams part on this issue.  There has to be a clean up process or something that cleans up the InstanceData table (this is the main culprit).

Have you noticed the growth of this database in your organization?

I’ll report back the results of the escalation once I have something new to report!

</rant>

Update – 6/9/2011

In working Microsoft, they have confirmed that the way the UPA currently works is by design and there is absolutely no process that will clean up the UPA database.  One workaround that was recommended was to simply delete and re-create the UPA services once a month.  Of course, this is asinine – I pushed the issue further to the SharePoint product team and ask them to provide either a hot fix or a supported method to purge these large tables.

Once I hear back from the product team, I’ll post another update.

Update – 7/12/2011

I received word from Microsoft that this issue is now with the escalation team – no more information than that.

One thing I wanted to point out is that in the environments where we saw extreme growth, we deleted the UPA and re-created it, reconfigured it, etc. and the database seems to be growing at a more reasonable rate. These steps were performed in more than one of our environments where we saw huge databases and it settled down. Something you might want to consider trying until Microsoft releases a better solution and/or workaround.

Update – 8/30/2011

I finally have a solution, well – “workaround”, to this problem.  Microsoft provided me with the stored procedure that is missing from SharePoint 2010’s version of Identity Management.

DISCLAIMER: Microsoft has told me that this script cannot be altered in anyway or I run the risk of being unsupported. Please use this at your own discretion.

Before running the script, you need to create a new schema in your SYNC database called FIM (I wonder where this stored procedure came from? haha).

Before running the script, please insure you change the database name to match that of your SYNC DB.

USE [Sync DB]
GO

CREATE SCHEMA FIM

GO

/****** Object:  StoredProcedure [fim].[TruncateInstanceData]    Script Date: 08/10/2011 14:09:11 *****/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [fim].[TruncateInstanceData]
AS
BEGIN

--************************************************************
--*                                                          *                                                         *
--*   Copyright (C) Microsoft. All rights reserved.          *
--*                                                          *
--************************************************************
SET NOCOUNT ON;
DECLARE @truncationTime datetime;
SET @truncationTime = DATEADD(day, -1, GETUTCDATE());
DELETE FROM [dbo].[InstanceData]
WHERE ([created] < @truncationTime)
END

After your have created this script, you can create a SQL scheduled job to execute it once a week, once a month, whatever.

Update – 12/29/2011

Depending on how the recovery mode of your database, you might notice that your TEMPDB will increase because of all the transactions that took place during the truncate.  Set your DB to SIMPLE recovery mode prior to running the initial stored procedure, or let your normal maintenance plans run and your databases should return to normal size.

Update – 4/16/2012

This probably has been solved in the February 2012 CU for SharePoint 2010.  The necessary jobs have been added to insure the database is cleaned up. See http://paulliebrand.com/2012/04/16/user-profile-database-growth-issue-resolved/

Adobe X was released at the end of last year and one of the feature points announced was “Native, seamless integration with Microsoft SharePoint”.  On the surface, everything appears to work correctly and solved one of the main issues with Adobe Acrobat with SharePoint I blogged about in the past.

Recently discovered that it is not as seamless as we would like. Adobe X does not appear to work with the following column types:

• Date and Time
• Person or Group
• Lookup

If you have a date and time column on a document library and you attempt to save the PDF back to SharePoint, you will receive the following error message:

File check in failed. The server said:

“Invalid date/time value

A date/time field contains invalid data. Please check the value and try again.”

Using Fiddler, I can see the SOAP request that the date field is being sent over as straight text. When using the lists web service, it requires the date to be in ISO 8601.

<?xml version="1.0"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Body>
    <UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
        <listName>{5398FF80-036F-40DB-92D5-264D72DE43B3}</listName>
        <updates>
            <Batch OnError="Continue">
                <Method Cmd="Update" ID="1"><Field Name="Title"/>
                    <Field Name="Date">05/17/2011 05:00:00</Field>
                    <Field Name="FileRef">http://portal.company.com/z/EditTest.pdf</Field>
                    <Field Name="ID">1</Field>
                    <Field Name="ContentTypeId">0x010100EAB6ED7FBD4A0042A6F9326944DCEE19</Field>
                    <Field Name="ContentType">Document</Field>
                </Method>
            </Batch>
        </updates>
    </UpdateListItems>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

The Person or Group field just shows the value as it stored within SharePoint:

-1#;Name

The Lookup field just does not show anything.

I hope Adobe is listening and releases an update that resolves these issues.

[Update]

We received word from Adobe that the “date” issue will be resolved in a release slated for September.  The other issues will be hopefully resolved in the version after that (next year).

We discovered a bug in SharePoint 2010 and the usage of the markup styles when you are editing a page.

To reproduce this issue, follow these instructions:

• Create a new page
• Click Markup Styles and select Heading 1
• Type “Test 1” and press enter
• Click Markup Styles and select Heading 2
• Type “Test 2” and press enter
• Click Markup Styles and select Heading 3
• Type “Test 3” and press enter

If you did this correctly, you should see the following as the end result:

On the surface, things may appear to be working correctly but lets take a closer look.  Click the HTML drop down and select Edit HTML Source.  You will quickly discover that the generated HTML is incorrect:

<h1 class="ms-rteElement-H1">Test 1</h1>
<h2 class="ms-rteElement-H2"><div>Test 2</div>
<h3 class="ms-rteElement-H3">Test 3&#160;</h3></h2>

Notice the H3 element is embedded within the H2 element causing the style of H3 to be different.

Go ahead and clear all the HTML and click OK. The follow the steps outlined above again but instead of pressing enter right away wait for 2-3 seconds before hitting it. You will also notice the blinking cursor will “pause” after typing. When you see it start blinking again it is safe to hit enter.

If you did this correctly, you should see the following as the end result:

You will notice that the style of “Heading 3” is different that during the first attempt.  If we examine the generated HTML, you will notice it is now correct:

<h1 class="ms-rteElement-H1">Heading 1</h1>
<h2 class="ms-rteElement-H2">Heading 2</h2>
<h3 class="ms-rteElement-H3">Heading 3&#160;</h3>

We contacted Microsoft and to cut a long story short they are not going to fix this issue.  It gets better – this problem only occurs in Internet Explorer.  Doing the same tests in Chrome will render different and more accurate results.  Also, SharePoint 2007 works as expected.

Just tell your content editors to work slower or have your IT staff roll out Chrome – thanks for another great feature!

SharePoint is like a bumble bee – technically it shouldn’t be able to fly but some how it does…

If you have used SharePoint 2007 and have moved to SharePoint 2010 you might have noticed that the toolbar is missing from the top of the asset picker. The user experience is horrible due to the fact that this toolbar is missing. A user needs to upload the image to the appropriate library first before they decide to edit the content. Way too many steps!

It has been confirmed by Microsoft that this is a bug and it will be fixed in Service Pack 1.  They have not provided a definitive release date for SP1 but estimated it to be around 2nd quarter.

2007

2010

Microsoft introduced the notion of Message Classification (http://technet.microsoft.com/en-us/library/aa998599(EXCHG.80).aspx) in Exchange 2007 and Outlook 2007. This allowed an organization to classify email and then generate transport rules that routed or manipulated those messages based on that classification.

Out of the box, Microsoft provides a list of actions (http://technet.microsoft.com/en-us/library/aa998315(EXCHG.80).aspx) that can be performed in a transport rule. One of the supported actions is prepend the subject with string. Unfortunately, prepending the subject line has some unfortunately side effects. For example, it will screw the sorting within Outlook of someone sorts by subject line.

Due to issues like that, I wanted to append the subject line instead and there was no out of the box way to do this.

That’s were building a Custom Transport Agent comes in. The process of building an actual custom transport agent is pretty trivial. There is already tons of information on the Internet about that specific topic. I would recommend starting here http://msexchangeteam.com/archive/2006/12/04/431755.aspx if you are new to that process.

The problem I had was there was no documented way of accessing the Message Classifications. After reviewing all the SDKs and searching endless on the Internet I kept coming up short. I worked with Microsoft and we finally arrived a solution.

The key to the whole process is asking the X-MS-Exchange-Organization-Classification header. The following code snippet is an example of how to retrieve it:

void ClassificationAgent_OnSubmittedMessage
    (SubmittedMessageEventSource source, QueuedMessageEventArgs e)
{

    var item = e.MailItem;

    try
    {
        var headers = item.Message.MimeDocument.RootPart.Headers;

        // This is the key to retrieving
        //the Message Classification value selected by the user
        var headerList = headers.FindAll("X-MS-Exchange-Organization-Classification");

        foreach (var header in headerList)
        {
            string classification = string.Empty;
            if (_classifications.TryGetValue(new Guid(header.Value), out classification))
            {
                // Perform whatever action you what
                // with the selected Message Classification
            }
        }

    }
    catch (Exception ex)
    {
        EventLog.WriteEntry("Classification Agent", ex.Message, EventLogEntryType.Error);
    }
}

You can download the Visual Studio 2010 solution sample (Liebrand.Exchange.Sample) I created from here: http://cid-06457d244696ab3c.office.live.com/browse.aspx/Code%20Samples

If you have ever wanted to access the Message Classification via a custom transport agent and could never figure out how to do it hopefully you will find this useful.

I decided to create another blog that is dedicated to one of my hobbies – photography. Over the next few weeks I’ll be uploading some highlights from over the last few years that I have been doing it. Some of the photos I have upload so far include my adventures at some SharePoint conferences (featuring Steve Ballmer, Tom Rizzo, and Huey Lewis and the News), my first wedding, some 4th July fireworks shots and many more.

Please take a moment to check it out and feel free to leave a comment. Check back often as I add more.

http://photography.paulliebrand.com

Thanks!

If you have ever done any threaded programming in .NET WinForms you have more than likely run into the following error message at some point:

InvalidOperationException was unhandled

Cross-thread operation not valid: Control ‘’ accessed from a thread other than the thread it was created on.

This exception is normally displayed while running in DEBUG mode. In release mode, everything appears to run as expected but at some point you will definitely run into a thread deadlock issue.

The following code represents a pattern I would use to get around this problem and I insured all the calls that would touch the UI from a different thread followed it:

private void button2_Click(object sender, EventArgs e)
{

    ThreadPool.QueueUserWorkItem(notUsed =>
    {
        for (int i = 0; i < 100; i++)
        {
            UpdateStatus(string.Format("Loading record {0} of 100", i, 100));
        }
    });
}

private void UpdateStatus(string message)
{
    if (InvokeRequired)
    {

        BeginInvoke((MethodInvoker)delegate() { UpdateStatus(message); });
        return;
    }

    textBox1.Text = message;
}

Although this pattern works it is not as elegant and can be further refined and simplified. To accomplish this I created an extension method for the Form as such:

    public static class FormExtensions
    {
        public static void HandleCrossThreadUI(this Form form, MethodInvoker code)
        {
            if (form.InvokeRequired)
            {
                form.BeginInvoke(code);
                return;
            }

            code.Invoke();
        }
    }

By modifying the original code above to make use of the extension I can simply the code a little further:

private void button2_Click(object sender, EventArgs e)
{

    ThreadPool.QueueUserWorkItem(notUsed =>
    {
        for (int i = 0; i < 100; i++)
        {
            UpdateStatus(string.Format("Loading record {0} of 100", i, 100));
        }
    });
}

private void UpdateStatus(string message)
{
    this.HandleCrossThreadUI(() => { textBox1.Text = message; });
}

This may not look like much but when you start adding more and more code, it becomes a lot easier to read.

Hopefully you find this a little useful.

We recently received the following error message when we attempted to build the cube within Project Server 2007:

Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server <server>. Error: ActiveX component can’t create object

TechNet has an article (http://technet.microsoft.com/en-us/library/cc197608(office.12).aspx) on this specific error message that basically says you need to make sure you have the SQL Server 2005 Analysis Services components installed on the Project Server application server.

• Microsoft SQL Server Native Client (sqlncli.msi)
• Microsoft SQL Server 2005 Management Objects Collection (sqlserver2005_xmo.msi)
• Microsoft SQL Server 2005 Backwards Compatibility Components (SQLServer2005_BC.msi)

Strange thing was we already had these components installed.

After troubleshooting the issue we determined that the DBAs installed a newer cumulative update on the SQL Server but the components where never updated in the Project Server 2007 environment.

Grabbing the updated components from the matching CU and upgrading those on the Project Server 2007 environment resolved the issue.

Just wanted to share in case anyone else runs into this specific scenario.

In 2008 I made a post about the infamous “locked for editing” message many people experience while using Office with documents stored in SharePoint. This post addressed the issue that can occur outside the normal “locking” mechanism Office and SharePoint.

I recently had a problem where I attempted to edit an Excel spreadsheet and I was presented by the following message:

Being a good SharePoint citizen, I waited about 20 minutes to give SharePoint the chance to unlock this file. Even using the Notify feature of Excel, I never received a notification saying it was available. I decided to follow the steps outlined in my other post on this topic. So I navigated to the Content.MSO directory, cleared it out so it was empty and tried to open the file again via SharePoint. To my surprise, I received the same message.

I was 100% sure that nobody else had this file open so I began investigating further. I ran a filemon on my computer as I was opening the file and saw it was attempting to read and write a file located under C:\Documents and Settings\<user>\Local Settings\Temporary Internet Files\6WBGVSLW\UKYNMWF7\Offline\e

I navigated to this folder and saw this:

I deleted all the content in this folder and attempted to open my document again and sure enough it opened successfully. This was the first time in the many years I have worked with SharePoint that I ran into this location.

I went to another computer and saw that the folder name after “Temporary Internet Files” was different. If I deleted this folder and opened the document again, the exact same name was created.

After some more research I found a registry key that is used to identify this location:

HLCU\Software\Microsoft\MSDAIPP\CacheFolderId

This value will identify the location on your computer since it will be different for everyone. The offline folder appears to be a special hidden folder so you might have to key it in manually once you have reached that destination.

For more information on what MSDAIPP (Microsoft Data Access Internet Publishing Provider) is, check out this Wikipedia article.

There still seems to be no idea what causes this problem to happen. Maybe it’s network error or a bug in one of the products – who knows! Regardless, this is just another location you might need to check to release the “locked by another user” error that so many people seem to get.

Hopefully this helps.

Devon and Christina’s wedding was the very first wedding I have ever been hired to shoot. As an aspiring photographer I was extremely nervous but they had the confidence in me to pull it off. Without a doubt, this was the most stressful photo shoot I have ever done but I think it all came together at the end. I still have much to learn both around my camera and shooting weddings in general.

We were able shoot Devon and Christina this close without them seeing each other before the wedding. It was amazing!

Congratulations to both of you and thank you again for this opportunity. I’ll never forget it.