Thread: Why isn't maintenance of the Profile table included in the aspdnsf_MonthlyMaintenance SP?

I upgraded from 8.x to 9.3 last October and I noticed that my database size is quickly approaching the max DB size for SQL Server Express 2008 R2. Some time ago I wrote a SQL Script to execute the aspdnsf_MonthlyMaintenance once a month using the Windows Task Scheduler on my server. Just today I discovered that the Profile table is very large, over 25 million rows. I checked and there is no code in the aspdnsf_MonthlyMaintenance stored procedure that deletes rows in the Profile table, although I see that there is an option to delete rows in this table if you manually run the maintenance from the admin app UI. Is there a reason this code was not added to the stored procedure? I quit using the UI to run this routine about a year ago since the screen would just timeout anyway and automating it is a much better approach.

There is more info here

http://forums.aspdotnetstorefront.co...nce-Automation

But this thread goes into how to stop writing (pointless) records to the profile table, which helps curb the problem at source. We use this.

http://forums.aspdotnetstorefront.co...ase-table-size

The edits should be made in App_Code\ASPDNSFProfileProvider.cs

I usually run this SQL script like this on an occasion (fortnightly) to keep it down

delete top(50000) from dbo.Profile where UpdatedOn < '2013-01-01'
delete top(50000) from dbo.Profile where UpdatedOn < '2013-01-01'
delete top(50000) from dbo.Profile where UpdatedOn < '2013-01-01'
delete top(50000) from dbo.Profile where UpdatedOn < '2013-01-01'
delete top(50000) from dbo.Profile where UpdatedOn < '2013-01-01'

obviously change the date. I usually keep the number of deletions down to cause as little disruption to the site as can be but you can increase this.

When it comes to Profile table it can be truncated. Without any major issues. I have mine set to truncate in a maintenance plan that runs monthly. I had it separate just to make sure there wouldn't be issues when I 1st tested things. I just never bother to combine it. However there is no reason that I can find that you you can't add truncate table dbo.Profile command to aspdnsf_MonthlyMaintenance. I would recommend putting the truncate command before the Defrag indexes command however.

I believe this was an issue in pre MS9200 carts only and was later fixed.

We have the truncating/cleaning of this Profile table in our Daily Maintenance service which most of our clients use: http://aspdotnetstorefront.maintenance.compunix.us/

So on top of keeping your cart/database optimized/clean (runs automatically nightly during off peak hours) it also cleans this Profiles table.

Thanks for all the posts everyone, I ended up adding the check to ASPDNSFProfileProvider SetProperty() method to not create rows with blank values or where CustomerID = 0 , although I am not 100% sure if those aren't legitimate anonymous customers in some cases. In my profile table, only 1.16% of the 25 million rows I had in there had a CustomerID > 0.

I also added the following to my maintenance script to delete any records over 30 days old:

Code:

DELETE FROM PROFILE WHERE UpdatedOn < dateadd(d, -30, getdate());

Originally Posted by jsimacek

I believe this was an issue in pre MS9200 carts only and was later fixed.

We have the truncating/cleaning of this Profile table in our Daily Maintenance service which most of our clients use: http://aspdotnetstorefront.maintenance.compunix.us/

So on top of keeping your cart/database optimized/clean (runs automatically nightly during off peak hours) it also cleans this Profiles table.

We upgraded from 8.x to 9.3 last October (thanks Jan!) and I can confirm that the issue is still there unless those records with CustomerID = 0 are somehow legitimate values, but I cannot really tell for sure from what my limited knowledge could determine looking through the code.