HQ DB Growth Issue

Hi there. My client has over 40 stores and counting due to expansion. RMS is running pretty well. Issue i have is that its a fashion house and has over 120 000 line items and counting. After 6 months, some items are not on the market and become redundant. Now after a year, the DB is now over 11GB and growing. What is the best way to handle this situation as the client continues to create more than 15 000 line items every 4 months due to seasonal fashion trends (average 60 000 items per year!). Redundant items are made inactive but still show up in sales, order reports. Any ideas would be welcome on this issue as now a 401 and other worksheets that go through items are taking more time to run than before.

Reply to
David
Loading thread data ...

David, Realistically these databases should not be this large. We have customers with hundreds of thousands of items that take up little to no room. Items are not that big. Data builds up the fastest in the Transaction, TransactionEntry, TaxEntry, TenderEntry and journal tables. Or possible a customization if you have one in place.

Where are you checking the database size? If you are looking in RMS Administrator, then you may be seeing an inflated log file. If you have Full SQL logging enabled, databases can get big quick. That is where I would look first. Honestly full logging may be great in an enterprise environment with dedicated DBAs in place, but for retail environments a good backup makes more sense then full logging.

Just my thoughts, if you have more follow up questions, let me know.

Thank you,

Ryan Sakry Program Manager snipped-for-privacy@rite.us

formatting link

320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax)
Reply to
rsakry

Lets see if i can get this in the right thread this time :)

Hi all,

Well I'm probably going to get flamed for this, but wth. I run a db for a chain of 25 stores, and our HQ database is 11g. It's also about 10 months old, and we don't have any performance issues. We are running an excellent server(Dual Processor 4g of ram) as our SQL server, and we only have around

1000 items. We also do not use any addons (yet), but our reseller (who shall remain nameless) says that our DB size is fine. They have experience with other chains who have databases of a much larger size than ours and they don't have any issues worksheet's or reporting.

That said, are you performing maintenance on these databases? The stores AND the hq? Are there any worksheets that haven't completed successfully? Is windows running well on these machines? I have scheduled weekly defrag's and daily restart.

It was recommended to me to periodically (quarterly was our solution) remove completed worksheets, and reindex the databases. Both require exclusive access to the database (No Pos, or manager running, no sync happening) but may help you some.

Hi Ryan

Thank you for the reply. They do a lot of transactions across all stores with two of their stores making about 500 transactions each every Friday, Saturday and Sunday. They have matrix items, do a lot of inter store transfers, POs. We do have add-ons to manage transfers and EFT. I will check the SQL logging settings. So far they have over 120

000 items and counting in the DB and some have been made inactive but I dont think this make the DB smaller. Which tables can I look at and how best can I make the growth of the DB more manageable at the current rate at which they add items and do a lot of transactions and inter store transfers?
Reply to
Wil J

David, I'd really need to have eyes on the data to be able to give you a full course of action. Generally speaking though, you dont want to ever delete out Item information, nor PO/Transfer data as this limits your ability to do reporting. You are correct that making an item Inactive does not reduce database size. Even deleting a single Item record does not save you much space, really an Item does not take up much room. I really believe the transaction log could be a culprit to check that out and let us know.

Thank you,

Ryan Sakry Program Manager snipped-for-privacy@rite.us

formatting link

320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax)
Reply to
rsakry

Hi..don't know if this helps or not but we ran into a similar issue where the store DBs were growing out of control. Follow the steps outlined under:

formatting link
This should correct the problem and prevent it from happening again.

Reply to
Burke, Tracylee

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.