Database is larger than it should be, why?

[scooter85]

We have been struggling with our site going down or timing out every 2-3 hours. I was told today that the database is over 150 gigs. Our store has about 5,000 items running the latest version of Able. We have it hosted with a local ISP that has been upgrading servers and switching things around but nothing is working. Does anyone have any ideas what could be creating that much information in the database or what to look at first?

Thanks,
Scott

[david-ebt]

It could be an issue with anonymous user records in your database. Take a look at this tech bulletin:

http://help.ablecommerce.com/upgrades/a ... enance.htm

[scooter85]

I can't get in right now and confirm our settings on our settings on anonymous users but we looked at that a few months ago and thought we had that set up correctly.

[david-ebt]

Do you have access to the database? Can you run a query directly against the database? If so, take a look at this link. It's a SQL query you can use to find out how much space each of your data tables is taking. That should help you track down the issues.

http://www.4guysfromrolla.com/webtech/032906-1.shtml

[scooter85]

No I don't have access to the database, but I sent that to our ISP who is looking at the database right now. He and I had a conversation about whether or not the maintenance program is reclaiming the deleted rows. Also there seems to be some spam making users with scrambled letters for names and information.

[scooter85]

Here is our maintenance setup page to show we only keep one set for 15 days and the other for 1 day.
Page tracking is set to 15 days also.

[dgoranov]

In MSSQL server database properties if the database "Recover Model" is being set to "Full" database transaction log file does not
get truncated after a database backup and can grow to terabytes. If your site database is located on one server and both mssql
data and transaction log files are on the same physical disk the database Recover Model should be set to Simple and transaction
log file truncated. The transaction log file is what is causing the issue, the actual database file is probably less then 10GB.

Ask your ISP to use Simple Recover Model and perform regular database backups.

[scooter85]

I'll have to find out what all they did, but everything is working fine now for a whole week. No time out errors in our log and we are not going down every 2-3 hours, or even at all since they got it fixed. I sent them your suggestions Dimi so I'll see what they have the msql settings at.

[scooter85]

Thought I would include the note from our ISP.

That is indeed what we found, and what [in conjunction with the server upgrades seems to have] stabilized the site. We switched to “simple recover” on 21 March.