April 30, 2008 at 12:46 am
Hi there
We have a Navision database. It's about 9gb. Some tables are being replicated overnight.
We have a maintenance plan running, that backups database and tries to shrink it.
This won't work because it is in in replication.
On top of that our transaction log grows quickly (yesterday it was ,6Gb, now it is 7gb).
So two questions :
- A maintenance plan on a database which is published should be possible ? How to do this ?
- How can a transaction log grow so quickly due to replication.
Fyi it is a transactional replication.
Many thanx in advance
Jeffrey
JV
April 30, 2008 at 6:43 am
The transaction log file growth can be managed by doing transaction log backups more regularly. The transaction log file will grow large enough to handle the transaction between backups. I would not expect a Navision database to have too many transactions to have a reasonable transaction log backup every hour (I did Navision consulting for awhile).
The Navision GUI and tools are all cursor-based row-by-row processing. Not MS SQL friendly, but it usually works pretty well unless poorly customized. It is pretty replication friendly except that it expects a lot of transaction management to ensure ledger consistency. So, transactions get pretty large and all individual transactions must be in the same transaction log file (you cannot back up just part of a transaction) so it is not completely unusual to see rather large transactions, but they should not be in the GB range.
If you manage your transaction log file size properly with regular log backups, you should not have to shrink the log file. It should stay at a pretty constant size. That is ok, allowing SQL to allocate what it "will" need for a transaction log size is good for performance. Having the file size regulary grow and shrink is less-good.
April 30, 2008 at 6:50 am
Hi
thx for your advise.
be aware, the maintenance plan consists out of backing up the tranaction log, but it will not truncate, because it is in replication.
How to do this ?
Thx
J
JV
April 30, 2008 at 6:54 am
Don't truncate the log file unless you really have to. A transaction log backup clears the log file but leaves it allocated on disk. Every time you do a transaction log backup, the SQL server starts over filling up that disk space. So, if the number and size of transactions between log backups is constant, your log file will stay the same size.
This is a good thing.
The trick is to make sure the size is reasonable - that is why you need to do regular transaction log backups. Use an interval that is not invasive but keeps the log file at a good size.
May 2, 2008 at 10:59 am
we have both repl from pub A to several subs S1,S2 etc and also Log-Shipping to DR
- works fine
all our Pubs are actually also Distributors, but it is possible to have a different box act as Distributor for either one Pub or several. May make sense for traffic (your mileage may vary) but makes this more of a single-pt-of-failure so plan carefully. Also external Distributor may take a while to scavenge the changes from the Pub's tranlog (hence it may grow to higher watermark).
as prev poster wisely said, it works well so don't break it! We have a weekend maintplan that does traditional reindex+shrink and that works great as the db is quiet and minimal tranlog usage.
HTH
Dick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply