3.1 Why am I running out of locks on the replicate side?
3.2 Someone was playing with replication and now the transaction log on OLTP is filling.
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.
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>
2> go
1> dbcc settrunc(ltm, ignore)
2> go
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. I do not know if it will work in situations that the previous example won't, or if it works for databases that are damaged or what. If someone knows when you use it and why, please let me know (mailto:dowen@midsomer.org).
1> sp_role "grant", sybase_ts_role, sa
2> go
1> set role sybase_ts_role on
2> go
1> dbcc dbrepair(dbname, ltmignore)
2> go
1> sp_role "revoke", sybase_ts_role, sa
2> go
This scenario is also very common if you load a copy of your replicated production database into development.