Deleting old files from SQL db

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   Hi,

I've been running a Product Cart store for a few years now and just realized from our host that we maxed out our SQL database capacity. I have increased the size of our db as a quick fix but would like to know how to delete some old files. What files can be deleted and how? Are there permissions that can be set to automatically delete some files after a certain amount of time has elapsed? Maxxing out the db size slowed the store down to a crawl when someone tried to check out and I would like to avoid this in the future.

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
Mfohr Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 21-April-2007 Location: Canada Status: Offline Points: 0	Post Options Post Reply Quote Mfohr Report Post Thanks(0) Quote Reply Topic: Deleting old files from SQL db Posted: 08-June-2010 at 12:06pm
	Hi, I've been running a Product Cart store for a few years now and just realized from our host that we maxed out our SQL database capacity. I have increased the size of our db as a quick fix but would like to know how to delete some old files. What files can be deleted and how? Are there permissions that can be set to automatically delete some files after a certain amount of time has elapsed? Maxxing out the db size slowed the store down to a crawl when someone tried to check out and I would like to avoid this in the future.

Greg Dinger Members Profile Send Private Message Find Members Posts Add to Buddy List Certified ProductCart Developers Joined: 23-September-2006 Location: United States Status: Offline Points: 238	Post Options Post Reply Quote Greg Dinger Report Post Thanks(0) Quote Reply Posted: 08-June-2010 at 12:17pm
	If you are using the XML tools, check your logging settings. A client recently discovered that 200mb of MSSQL was chewed up due to full logging. If you are using the Quickbooks Synch tool, that also has log data that can be purged. If you are using BTO, there is a database cleanup tool under the help menu that removes expired data. You should identify what version code and what features you are running in order to further define what options may be available to clean up your database. Beyond those, I would really like to see PC feature a selective purge tool that would allow the merchant to remove order data that is earlier than some specific date so as to clear out tables such as "productsordered" and whatever else chews up space. The idea would be to ascertain the date beyone which order data is no longer important and nuke it, thus regaining space in the database.

Mfohr Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 21-April-2007 Location: Canada Status: Offline Points: 0	Post Options Post Reply Quote Mfohr Report Post Thanks(0) Quote Reply Posted: 08-June-2010 at 12:27pm
	Sorry, Running V3.051 No custom mods to PC. No QB. No BTO. Thanks, Mike

Greg Dinger Members Profile Send Private Message Find Members Posts Add to Buddy List Certified ProductCart Developers Joined: 23-September-2006 Location: United States Status: Offline Points: 238	Post Options Post Reply Quote Greg Dinger Report Post Thanks(0) Quote Reply Posted: 08-June-2010 at 12:41pm
	check your PM

Greg Dinger Members Profile Send Private Message Find Members Posts Add to Buddy List Certified ProductCart Developers Joined: 23-September-2006 Location: United States Status: Offline Points: 238	Post Options Post Reply Quote Greg Dinger Report Post Thanks(0) Quote Reply Posted: 08-June-2010 at 12:48pm
	So how big is your SQL database?

Mfohr Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 21-April-2007 Location: Canada Status: Offline Points: 0	Post Options Post Reply Quote Mfohr Report Post Thanks(0) Quote Reply Posted: 08-June-2010 at 12:54pm
	Had to bump SQL plan to 200mb from 100mb because it was over.

Greg Dinger Members Profile Send Private Message Find Members Posts Add to Buddy List Certified ProductCart Developers Joined: 23-September-2006 Location: United States Status: Offline Points: 238	Post Options Post Reply Quote Greg Dinger Report Post Thanks(0) Quote Reply Posted: 08-June-2010 at 1:23pm
	There may not be much you can do, given stuck functionality, to reduce the database table sizes. With a store that has been in existence for this long, just the longevity of the site and therefore cumulative order data, the database is going to grow. With that said, one thing you will want to ask your host is if the DB is set for simple logging, and not full logging. That alone would make a difference, but its probably already set for simple. Worth checking though.