none
User Profile Service Application - Profile DB/database is growing rapidly RRS feed

  • Question

  • Hi,

    We had setup a new environment and we are seeing an issue which we have never come across all these days.

    Profile Database of USPA is growing more than 200 GB and we are running out of space in database.

    We got around 50000 profiles same as other environment where the DB size is around 4 GB.

    First we installed APP/WFE in same machine then we tried to move WFE to dedicated server. I hope this shouldn't have caused any issue but this is the only thing which we had done newly when compared with other environment.

    User Profile Service Application - Activity Feed Cleanup Job, Runs and cleans up around 100 GB of data, but again it grows to 200+ GB.

    This is newly set environment and not more than 5 users are there in this new environment.

    Currently all our environments got APP?WFE in same server so we are planning to scale up that is where we are finding this issue. Really we cannot afford to delete USPA and recreate as we got heavy usage in Production.

    Was curious to know which table is taking more space and below is the split. User Profile Service Application - Activity Feed Cleanup Job is cleaning up only 100 + GB and then it is getting filled.

    name

    rows

    reserved(KB)

    data(KB)

    index_size(KB)

    unused(KB)

    Total Size(KB)

    ActivityEventsConsolidated

    19210344

    114106048

    111345088

    2734408

    26552

    114079496

    UserColleagues

    13707658

    1993424

    778648

    1214168

    608

    1992816

    ActivityEventsPublished

    51487

    353688

    348192

    5320

    176

    353512

    UserProfileValue

    1784839

    324512

    171432

    151904

    1176

    323336

    UserProfileEventLog

    616548

    174992

    100696

    73896

    400

    174592

    Profile_SearchUserTable

    487106

    63208

    32408

    29632

    1168

    62040

    UserProfile_Full

    46494

    39872

    13272

    25904

    696

    39176

    DNLookup

    46450

    13408

    6616

    6392

    400

    13008

    Profile_SearchUserTermStats

    182890

    11320

    10920

    216

    184

    11136

    PropertyList

    128

    120

    40

    80

    0

    120

    Please provide some thoughts and Microsoft recommended approach to resolve this issue.

    Thanks

    Mani


    • Edited by Manikanth B Wednesday, May 18, 2016 3:54 AM Added Split of table which is consuming more size
    Wednesday, May 18, 2016 3:47 AM

Answers

  • Thanks. ActivityEventsConsolidated don't necessarily require any user interaction on SharePoint, simply importing users to SharePoint is enough to generate data in that table.

    Do both farms synchronize the same OUs/have the same filters, and use the same methods for synchronization?


    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, May 19, 2016 4:22 PM
    Moderator

All replies

  • Today we noticed that it has gone up to 212 GB and we don't have any free space in database drive.

    Even  User Profile Service Application - Activity Feed Cleanup Job is not doing much.

    Can anyone provide some inputs?

    Thanks

    Mani

    Wednesday, May 18, 2016 11:36 PM
  • Is the Full Recovery model enabled? If so, are you taking frequent T-Log backups to prevent LDF growth?

    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, May 18, 2016 11:45 PM
    Moderator
  • Hi Trevor,

    It is currently set to SIMPLE recovery model.

    Thanks

    Mani

    Thursday, May 19, 2016 1:23 AM
  • Have you tried reducing the number of days activities are kept for?

    https://blogs.msdn.microsoft.com/ronalg/2012/03/26/user-profiles-database-sizes-and-daysworthofactivityfeedstokeep/


    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, May 19, 2016 1:42 AM
    Moderator
  • Yes I tried by modifying it to just for 2 days. Earlier it shrinked from 200+ to 100+ GB,  within one day it again increased to 200 GB and now it is not shrinking at all.

    Thanks

    Mani

    Thursday, May 19, 2016 3:04 AM
  • Thanks. ActivityEventsConsolidated don't necessarily require any user interaction on SharePoint, simply importing users to SharePoint is enough to generate data in that table.

    Do both farms synchronize the same OUs/have the same filters, and use the same methods for synchronization?


    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, May 19, 2016 4:22 PM
    Moderator
  • Hi Manikanth,

    I am having the same issue now. Have you find a solution to this problem?

    Monday, May 1, 2017 2:56 PM
  • Hi Trevor,

    Yes both the farms synchronize same OU's and filters. Still it didnt work.

    We have deleted the user profile service application along with all the database and recreated it.

    Almost for a year it was running fine and again all of a sudden the issue started occurring.

    Again we are going to delete and recreate the USPA.

    Not sure if many customers are seeing this kind of issue. But rebuilding USPA is a really a painful process and time consuming.

    Regards,

    Manikanth

    Thursday, September 14, 2017 2:55 AM
  • Hi Jimmy,

    No solution has been found yet. We are just deleting the USPA and recreating it. Which is not the ideal solution but we are not left with many choices.

    Manikanth

    Thursday, September 14, 2017 2:56 AM