DB repair- help!

[Brewhaus]

Our DB, virtually from when we first moved to AC a few years ago, is not properly running the anonymous user cleanup routine, and as a result our data file is now roughly 1.5GB. I am sure that this is contributing to our sites slowing. Regardless, the file size seems to be getting out of hand, so we really need to fix this. Can anyone help?

[batmike]

Rick,

I have this code from a while back in another part of the forums here that you can run manually or set up as a SQL job to clean up the anonymous users and their associated details. The first block is just a SELECT statement so you can see how many users you'd actually be affecting by running the next three statements. You can change the dates as you need to remove more or less data and you can also modify the AffiliateId IS NULL part to IS NOT NULL if you want to remove anonymous users who also have an affiliate assigned to them (if you even use that functionality).

I would suggest running the SELECT first and even try doing a SELECT on all three tables to make sure there's no strange data pulled back that you don't want to delete. You could also try it out on a duplicate test database, which is always a good idea

In any case, this should at least help clean up some of that old data. As far as the maintenance routine not working, I'm not really sure what's going on there. Ours doesn't report any problems, but that doesn't mean it's working necessarily, though I honestly haven't checked in a while.

SELECT statement for Anonymous Users:

Code: Select all

SELECT UserId FROM ac_Users 
WHERE StoreId = 1 AND IsAnonymous = 1 
AND AffiliateId IS NULL 
AND (LastActivityDate IS NULL OR LastActivityDate < '2011-01-01')

Statements to remove Anonymous Users Baskets, Wishlists and Users. Run in order.

Code: Select all

DELETE FROM ac_Baskets 
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL) 
AND (LastActivityDate IS NULL OR LastActivityDate < '2011-01-01'))

DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL) 
AND (LastActivityDate IS NULL OR LastActivityDate < '2011-01-01'))

DELETE FROM ac_Users 
WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL) 
AND (LastActivityDate IS NULL OR LastActivityDate < '2011-01-01')

[Brewhaus]

Thank you for the help on this. Just to confirm- we would do this in SQL Query Analyzer, correct?
And, we would run the jobs separately?

[batmike]

Yes you could run any of them using the Query Analyzer. I would run the SELECT first by itself to see some of the data that will be affected, make sure it looks good and such. Then you can run each of the DELETE statements one at a time.

And I suppose to cover myself, I would recommend making a backup of the database first so that you can revert if you need to

Also, your StoreId may be difference so adjust that accordingly in the queries.



Finally, as for having a job run it. That would require access to the SQL Server Agent on the database system and you could save the delete queries to a stored procedure and then use the Agent to run those at whatever interval. There may be other methods for scheduling SQL queries, but that's how I've got it set up for an inventory query that I have run nightly.


Mike

[Brewhaus]

Out of curiosity I ran the SELECT for ac_Users for everything prior to 12/31/12 (do you have a suggestion on how far back we should really keep the anonymous user information? I figure a couple of months is more than enough). It returned just under 1.2 million records to remove. Before I proceed with removing these anonymous users / baskets / wishlists, do you think that the immense number of records would contribute to SLQ's CPU usage and our website speed?

[batmike]

Well, our maintenance routines are working well and I have the anonymous users set to delete after 14 days (with affiliate association or not). So a couple months would certainly keep you safe too.

I imagine that the large number of records could slow things down a bit. I know that with our search engine system, we had data piling up in those tables and it was slowing down the reporting to the point where it couldn't even generate anything. I'd like to think that the queries throughout the shopping cart are targeted enough not to drag things down on the front end though I'm not a SQL expert by any measure so I guess I couldn't answer that one way or another with all that much certainty. For my part, I just like to see things kept cleaned up so that there aren't lots of old things floating around.

Anyway, I hope that helps somewhat and at least lets you know about removing the older data if not the performance stuff.


-Mike

[Brewhaus]

No luck with the scripts. When run they come back with 0 rows affected and no anonymous users are deleted. We are having a problem (and have had virtually forever) with the constraints on the DB not allowing the users to be deleted. Any thoughts on this?

Rick

[Brewhaus]

Can anyone help on this? Would exporting the DB, and then importing it into a new DB file work, or will the contraints be brought in with the import? We are desperate to get our file size down, and maybe even more importantly, to stop the growth resulting from the inability to purge the old users, baskets, etc.

[Tea-Dev]

Did you ever resolve this. We have run into this issue now.

Tech support was not helpfull.

We are looking into MS SQL 2008 R2 issues that maybe keeping ABLE from deleting records.

[Brewhaus]

When we migrated to Windows 2008R2 and SQL2008 the issue was resolved. We did have to run a small query, if I recall (available on the AC site), but it would never work when we were on W2003 and SQL2000.

[Tea-Dev]

also is there a reason when the maintenance job runs it does not trip the ac_addresses table?

[Katie]

We have compiled all the information on fixing maintenance issues here:

http://help.ablecommerce.com/index.htm# ... enance.htm

Review the entire document first. The last parts have queries that can be run directly on the database to fix these issues.

Once things are cleaned up, be sure to set the maintenance intervals to a reasonable number of days. A value of 30 days is recommended.

Thanks,
Katie

[Tea-Dev]

Katie is there a reason teh ac_addresses table is not included in the trim?

[Tea-Dev]

Katie
I reviewed the entire document in http://help.ablecommerce.com/index.htm# ... enance.htm and there is no info on how to manually trim the ac_addresses, ac_baskets, ac_wishlist, ac_users...

[Katie]

Please see the post above by batmike » Wed Feb 06, 2013 6:41 am

He has posted the SQL queries to remove Anonymous Users Baskets, Wishlists and Users. And yes, we should have this written into the document link I provided. We'll get that taken care of asap.

Adjust the date as desired for what point in time you want to eliminate users. If the script times out, there are too many users in the result set. In that event, set the date farther back in time and run the query. Then move it forward in steps (perhaps a week at a time) until you reach the desired cutoff. That will limit the number of records processed at any one time.

If the automatic maintenance is failing, then a possible cause might be a constraint issue in the database. In that case, these scripts may trigger the same error.

An anonymous user is one that has not registered or placed an order, so that might be the reason the ac_addresses table was not included.

Please let us know if you have any luck with this.

Katie

[jmestep]

Oh, so someone is spamming again?