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.
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
da.Fill(dt)
Dim Count As Integer = 0
For Each row As DataRow In dt.Rows
UserName = CStr(row("UserName"))
Try
UserProfile = Profile.GetProfile(UserName)
If Not UserProfile.RecentlyViewedProducts Is Nothing Then
UserProfile.RecentlyViewedProductIDs = UserProfile.RecentlyViewedProducts.GetRecentlyViewedProductIDsReverseOrder
UserProfile.RecentlyViewedProducts = Nothing
UserProfile.Save()
End If
Catch
End Try
Count += 1
Next
End Sub
-NightOwl888
Microsoft Certified Technology Specialist
Marked as answer by NightOwl888 on Mar 28, 2010 11:30 AM
NightOwl888
Member
15 Points
32 Posts
How do I REMOVE fields from the ASP.NET profile provider and database?
Mar 25, 2010 07:25 PM|LINK
Hello,
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
Microsoft Certified Technology Specialist
NightOwl888
Member
15 Points
32 Posts
Re: How do I REMOVE fields from the ASP.NET profile provider and database?
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 da.Fill(dt) Dim Count As Integer = 0 For Each row As DataRow In dt.Rows UserName = CStr(row("UserName")) Try UserProfile = Profile.GetProfile(UserName) If Not UserProfile.RecentlyViewedProducts Is Nothing Then UserProfile.RecentlyViewedProductIDs = UserProfile.RecentlyViewedProducts.GetRecentlyViewedProductIDsReverseOrder UserProfile.RecentlyViewedProducts = Nothing UserProfile.Save() End If Catch End Try Count += 1 Next End SubMicrosoft Certified Technology Specialist