From what I understand, SQL Server Express 2008 R2 has a database size limit of 10GB.  I ran a query that returns the Data and Log file sizes, and my database is currently at 10145 MB (9.89 GB).  Am I very close to hitting a limit that will start to throw exceptions on the database?  If so, is there a good script out there that people use for a 9.2 ASPDNSF database to purge old order information and other rows that will get my database back down to a smaller size?  My system has been online since early 2007 so I'm sure there are a lot of records in there that I could purge.  I was thinking that maybe clearing out any closed orders over 3 years of age would be a good approach - or does anyone have any other ideas?  I would rather not upgrade SQL Server unless I absolutely have to.


in MultiStore by (145 points)

1 Answer

0 votes
In version 9.2, the Profile table is probably the place to start before you look at archiving off old orders (which isn't a bad idea either).

That table can grow very large very quickly in that version.  Starting in version 9.3 we made some improvements to slow down that growth, and made sure that monthly maintenance cleaned that table up as well.  We also made some changes in 9.4 that shrink the Customer and Orders tables pretty significantly if you're using realtime shipping rates.

If you can't upgrade right away, consider deleting from the Profile table periodically.  We generally recommend nuking everything older than a week or so each time you clean up that table.
by (13.5k points)

Yeah, I had already taken care of the Profile table awhile back.  I was wondering if the 4 tables mentioned in the documentation is still all that need to have order information purged from - I don't want to create any issues with orphan records:


What about these other tables that contain the OrderNumber field?


Should rows with the purged OrderNumber be cleared from these tables as well? I just wanted to make sure that the docs listed below still apply to 9.3: (just checked, I'm actually on 9.3 now)

The profile.dbo is the obvious culprit. I personally would not remove anything from the orders tables. Do you use the mail manager within ASPDSF to send out any newsletters?

If so you may want to check the MailingMgrLog table as this keeps a record of all emails to customers and each row contains the html of that email. Can be one that grows.
No, actually neither of those are very large.  I have a query that returns total space used  by table and the profile table comes in at # 10 and the MailingMgrLog table has zero rows (we use Constant Contact). I know exactly which tables are taking up the most space, with the top 5 being:

Orders:                            101146 rows, 1203120KB used
Orders_ShoppingCart: 200257 rows,  536448KB used
Customer:                       71879 rows,  407136KB used
SecurityLog:                    445864 rows,  299632KB used

I was just hoping that someone had developed a good script to clear old order data as the one in the manual seems very simplistic and doesn't address several other tables with foreign keys to the OrderNumber column or address other tables like SecurityLog or SearchLog, which I assume could be trimmed as well.

Well to clear the securitylog you can view securitylog.aspx page or run this script.

delete from SecurityLog where ActionDate < dateadd(year,-1,getdate())

Not sure why aspdnsf_MonthlyMaintenance doesn't have a statment like it but it would be easy to add on your own.

As for the other tables like others have said try clearing some of your real time shipping information. As Scotts said aspdnsf_MonthlyMaintenance does clear out real time shipping information in version 9.4. If you don't have that or just want a script to do so based off a date then try this sql script:

UPDATE dbo.Customer SET RTShipRequest = null, RTShipResponse = null
WHERE CreatedOn < dateadd(d,-30,getdate())

UPDATE dbo.Orders SET RTShipRequest = null, RTShipResponse = null
WHERE CreatedOn < dateadd(d,-30,getdate())

This old fourm thread has at least a few reports examples you can run to find what else you can clean out.