AbleCommerce Gold How To Clean Up Anonymous Users

by Joe Payne 14. April 2014 15:45

Even though Able Gold has a manual cleanup option in the Maintenance page, it doesn’t always work well.  The problem arises from how Able must delete each user individually.  Not such a big deal when you have 500 users to clear out.  

It’s a very different story when you have 2,000,000 unwanted users.   A SQL query can delete all of the unwanted records in a single command.

Below is the updated query to work with Able Gold schema.  Obviously change the dates to something more recent. 

If the queries do not remove as many records as you expected, you might want to remove the “AND (AffiliateId IS NOT NULL)” criteria if you don’t care about affiliate reporting.

use <yourdbname>
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 <'June 30, 2009'))
 
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 <'June 30, 2009'))
 
DELETE FROM ac_Users 
WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009')

Tags: , , ,

AbleCommerce Gold | Tech Support

SQL Studio Management Expand Databases Slow

by Joe Payne 14. April 2014 10:53

So on my development PC, I’ve noticed that it takes 2-3 minutes for the Databases node to expand in my SQL Server Studio Management 11.0.2100.60

I don’t remember exactly when it started.   But it’s been several months I think.  It was just too easy to ignore since I could alt-tab and do something else while it worked through the delay.

Today I decided to research it and determined it’s because some databases have auto_close turned on.   This causes the SQL server to have to start up the database before it can render it in the Databases node.    This creates the significant delay when you have several databases configured for auto_close.

A quick way to fix all of them at once time AND set recovery mode = simple (anything more is useless in my dev environment), use this query:

USE MASTER
declare
    @isql varchar(2000),
    @dbname varchar(64)
    
    declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
    open c1
    fetch next from c1 into @dbname
    While @@fetch_status <> -1
        begin
        select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        select @isql='USE @dbname checkpoint'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        
        fetch next from c1 into @dbname
        end
    close c1
    deallocate c1

Tags: ,

Personal | Tech Support

Month List