Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Mar 28, 2010 11:29 AM by NightOwl888
Mar 25, 2010 07:25 PM|LINK
I have an issue where I am running out of database space on my hosting provider. As it turns out, the aspnetdb database is more than 6x our application database. We are using anonymous profiles and there are 21 fields stored in each user profile.
Upon analysis, most of this data is not required to be stored - it is simply wasting space. The part that is wasting the most space (5 binary objects) are not even used by any of the public users. I would like to remove these binary objects from all of the
user's profiles without deleting the profiles, but I can't seem to find any documentation on how to do this.
I can remove the fields, which will take care of users going forward, but I need to retroactively delete these fields from the aspnetdb database to free up space. Since each user manages their own profiles, I am unsure how to create an automated procedure
to delete these fields out each anonymous user's profile.
remove custom profile fields
asp.net profile provider
Mar 28, 2010 11:29 AM|LINK
Well, since I didn't get a response I had to explore on my own. I started using Reflector to go through the profile class - I was going to reverse engineer the code so i could update the binary information.
Then I discovered the answer - there is a GetProfile method on the ProfileCommon class, which accepts the username as an argument. From there it was just a matter of getting a list of all of the usernames I needed to update and performing the updates on
the profile object as if were inside each user's session. Here is the code I used (in my ASP.NET application):
Private Sub ConvertProfiles()
Server.ScriptTimeout = 900000
Dim conn As String = ConfigurationManager.ConnectionStrings("SqlServices").ConnectionString
Dim cn As New SqlConnection(conn)
Dim cm As New SqlCommand("SELECT UserName FROM aspnet_Users U (NOLOCK) WHERE U.UserId In(SELECT TOP 50000 UserId FROM aspnet_Profile P (NOLOCK) WHERE P.PropertyNames Like '%RecentlyViewedProducts%' AND P.PropertyNames Not Like '%:-1:%') ORDER BY UserName DESC", cn)
Dim dt As New DataTable
Dim da As New SqlDataAdapter(cm)
Dim UserName As String = ""
Dim UserProfile As ProfileCommon
cm.CommandTimeout = 300000
Dim Count As Integer = 0
For Each row As DataRow In dt.Rows
UserName = CStr(row("UserName"))
UserProfile = Profile.GetProfile(UserName)
If Not UserProfile.RecentlyViewedProducts Is Nothing Then
UserProfile.RecentlyViewedProductIDs = UserProfile.RecentlyViewedProducts.GetRecentlyViewedProductIDsReverseOrder
UserProfile.RecentlyViewedProducts = Nothing
Count += 1