magento-delete-abandoned-carts.sql

-- deactivate all abandoned shopping carts belonging to noone
UPDATE sales_flat_quote
SET is_active = 0
AND updated_at < '2012-12-05';

-- remove all deactivated shopping carts
DELETE FROM sales_flat_quote WHERE is_active = 0;

Observer.php

<?php

/*
To make the cleaning expired carts automatic, override cleanExpiredQuotes with the following
version which will also take care of the old active carts: app/code/core/Mage/Sales/Model/Observer.php
*/

public function cleanExpiredQuotes($schedule)
{
    // extending limit
    ini_set( "memory_limit", "2000M" );

    $lifetimes = Mage::getConfig()->getStoresConfigByPath('checkout/cart/delete_quote_after');

    // cleaning expired quotes that have been converted to orders
    foreach ($lifetimes as $storeId=>$lifetime) {
        $lifetime *= 86400;

        $quotes = Mage::getModel('sales/quote')->getCollection();
        /* @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */

        $quotes->addFieldToFilter('store_id', $storeId);
        $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));
        $quotes->addFieldToFilter('is_active', 0);

        $quotes->walk('delete');
    }

    // cleaning expired quotes that have not been converted to orders
    // change by [email protected]
    foreach ($lifetimes as $storeId=>$lifetime) {
        $lifetime *= 86400;

        // let's be safe and allow double of the expiration period for those quotes that have not been
        // converted to orders
        $lifetime *= 2;

        $quotes = Mage::getModel('sales/quote')->getCollection();
        /* @var $quotes Mage_Sales_Model_Mysql4_Quote_Collection */

        $quotes->addFieldToFilter('store_id', $storeId);
        $quotes->addFieldToFilter('updated_at', array('to'=>date("Y-m-d", time()-$lifetime)));

        // not converted
        $quotes->addFieldToFilter('is_active', 1);

        $quotes->walk('delete');
    }

    return $this;
}