Why am I running out of locks on the replicate side?
Sybase replication works by taking each transaction that occurs in the
primary dataserver and applying to the replicate. Since replication works on
the transaction log, a single, atomic, update on the primary side that updates
a million rows will be translated into a million single row updates. This may
seem very strange but is a simple consequence of how it works. On the primary,
this million row update will attempt to escalate the locks that it has taken
out to an exclusive table lock. However, on the replicate side each row is
updated individually, much as if they were being updated within a cursor loop.
Now, Sybase only tries to escalate locks from a single atomic statement (see
ASE Qx.y), so it will never try to escalate the lock. However, since the
updates are taking place within a single transaction, Sybase will need to take
out enough page locks to lock the million rows.
So, how much should you increase the locks parameter on the replicate side?
A good rule of thumb might be double it or add 40,000 whichever is the larger.
This has certainly worked for us.
Transaction log on OLT is filling.
Once replication has been configured, ASE adds another marker to the
transaction log. The first marker is the conventional one that marks which
transactions have had their data written to disk. The second is there to
ensure that the transactions have also been replicated. Clearly, if someone
installed replication and did not clean up properly after themselves, this
marker will still be there and consequently the transaction log will be filling
up. If you are certain that replication is not being used on your system, you
can disable the secondary truncation marker with the following commands:
1> use <database>
1> dbcc settrunc(ltm, ignore)
The above code is the normal mechanism for disabling the trucation point. I
have never had a problem with it. However, an alternative mechanism for
disabling the truncation point is given below.
1> sp_role "grant", sybase_ts_role, sa
1> set role
1> dbcc dbrepair(dbname,
1> sp_role "revoke", sybase_ts_role, sa
This scenario is also very common if you load a copy of your
replicated production database into development