AspDotNetStorefront Monthly Maintenance Time-Out Error

Version: AspDotNetStorefront 9.0.1.3 (including multi-store and SP1)

Problem:

When running monthly maintenance a timeout error is generated.

Solution:

Run the monthly maintenance SQL directly on the database.  Important: only attempt this if you have a full backup of your database and have inspected each and every SQL parameter below and understand the full implications of running this stored procedure.  This is meant as a technical reference for developers and database administrators only.

aspdnsf_MonthlyMaintenance @InvalidateCustomerCookies=0, @PurgeAnonCustomers=1, @CleanShoppingCartsOlderThan=0, @CleanWishListsOlderThan=0, @CleanGiftRegistriesOlderThan=0, @EraseCCFromAddresses=1, @EraseSQLLogOlderThan=30, @ClearProductViewsOrderThan=180, @EraseCCFromOrdersOlderThan=30, @DefragIndexes=1, @PurgeDeletedRecords=0

In version 9.x, the monthly maintenance admin routine will also “Clear Profiles Older Than”.  This is a separate SQL statement not included in the stored procedure above.  The Profile table can grow large quickly and old data must be removed.  e.g. delete profile table rows > 30 days old:

delete from Profile where UpdatedOn < dateadd(d, -30, getdate())