Replacing stsadm -o sync with PowerShell

One of the only stsadm.exe commands I use today is stsadm -o sync. I thought it would be a great idea to replace this ‘final’ stsadm command with PowerShell. I will be covering two equivalent cmdlets, one to cover stsadm -o sync -listolddatabases 0 and another to cover stsadm -o sync -deleteolddatabases 0. What these specific commands do is clear the last sweep time that the User Profile Service Application timer jobs pushed User Profile Information from the Profile database into each Content Database for use within the UserInfo lists on Site Collections. The -listolddatabases 0 is part of the troubleshooting process to see when this push last ran successfully. Typically if it is older than one day, you would use -deleteolddatabases 0 to clear out that synchronization information. The next time the UPSA -> UIL timer jobs ran, the UserInfo data in Site Collections would be up-to-date.

This module provides two cmdlets:

Both parameters allow you to pass an integer representing the number of days. If no value is passed, 0 will be used (which will most likely report all databases). The script also validates that there is an available User Profile Service Application Proxy in the farm, as well as attached to the Web Application. Content Databases in Web Applications that do not have a UPSA Proxy will be skipped. Save the below script as SharePointDatabaseSync.psm1. Use Import-Module to import it into the current PowerShell session, or place the script in C:\Users\username\Documents\WindowsPowerShell\Modules\SharePointDatabaseSync\. This will allow the module to automatically load on Windows Server 2012 or higher.

Ultimately, the output will look similar to the pictures below.

DatabaseSyncInfo

 

If you have any thoughts or possible improvements, let me know.

7 Comments

  1. This provides Clear-SPDatabaseSyncInformation not Remove-SPDatabaseSyncInformation

  2. Pingback: Newsletter – Episode 59 – Belarus SharePoint Community Blog

  3. Trevor, when running this for each database I am getting this error

    Clearing Sync Data for 15-025
    Exception calling “ClearSyncDataForContentDatabase” with “1” argument(s):
    “UserProfileApplicationNotAvailableException_Logging ::
    UserProfileApplicationProxy.ApplicationProperties ProfilePropertyCache does
    not have 53180e55-7733-41da-8126-05129efd08c1”
    At F:\tcsc\Scripts\SharePointDatabaseSync.psm1:71 char:17
    +
    [Microsoft.Office.Server.UserProfiles.WSSProfileSynch]::ClearSyn …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : UserProfileApplicationNotAvailableException

    • This generally means that the account running the SharePoint Mgmt Shell does not have Administrator rights on the UPSA (under ‘Administrator’ and ‘Permissions’ tabs — you need Full Control). You need to close and reopen the Mgmt Shell for the permissions to take effect. It can also mean that the Application Pool running the UPSA isn’t running.

  4. That was it exactly. Works like a champ. Thanks for taking the time to put this together and sharing with the community.

  5. This looks like what I need! All my content DBs have no entry for the last sync, and I think this is causing your code to error:

    ToLocalTime : You cannot call a method on a null-valued expression.
    At E:\scripts\SharePointDatabaseSync.psm1:38 char:52
    + if ($db.LastProfileSyncTime.ToLocalTime <<<< () -lt $now)
    + CategoryInfo : InvalidOperation: (ToLocalTime:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Any chance of a code tweak?

    Thanks!

Leave a Reply