HQ DB Growth Issue
David
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.
Ryan @ RITE
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
rsa...@rite.us
http://www.rite.us
320-230-2282 ext. 4002 (Office)
320-230-1796 (Fax)
David
> 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
> 320-230-2282 ext. 4002 (Office)
> 320-230-1796 (Fax)
>
> On 7/16/2010 4:54 AM, David wrote:
>
>
>
> > 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.
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?
Wil J
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.
"David" <dsa...@gmail.com> wrote in message
news:965133b2-6bc0-4284...@k39g2000yqb.googlegroups.com...
Ryan @ RITE
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
rsa...@rite.us
http://www.rite.us
320-230-2282 ext. 4002 (Office)
320-230-1796 (Fax)
Burke, Tracylee
the store DBs were growing out of control. Follow the steps outlined under:
https://mbs.microsoft.com/knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmymxtyykswtlkqnnoxsppyozzuvxrmkutr
This should correct the problem and prevent it from happening again.
"Ryan @ RITE" <rsa...@rite.us> wrote in message
news:eGSl6ybK...@TK2MSFTNGP04.phx.gbl...