It has been a few days, and I have a few minutes ...
- This is not oracle. I say this just so that you can understand that virtually all log space management in Sybase is automatic (and the bits aren't can be handled by scripts or thresholds). You never have to fiddle with tablespaces or syslogs, or figure out where the data is, so you need not seek to do so.You also appear to think that the uncommitted transactions are "stored" in syslogs. For purposes of understanding (simplicity):
- "trunc with no_log" is not a db_option, it is an option in the command "dump tran <dbname>", and which is only to be used in the worst emergency (truncate_only is slightly less worse; both deem the db unrecoverable)
- you should not truncate the log (except in emergency), it is truncated when you dump (the Inactive portion of) it
- normal use is "dump tran <dbname> to <file>"
- The data is ALWAYS in the tableAnd another thing. Transaction size
- any given page (addressable piece) in syslogs can be said to be in one of three categories: Active (uncommitted transactions); Inactive (committed transactions); and Free (not used at all). It is a circular file, so just think of it as 3 summary figures.
- Update commands cause rows in the table to be locked and the uncommitted CHANGES are in syslogs/Active
- Committing the tran writes the CHANGES from syslogs/Active to the table, and releases the space from syslogs/Active to syslogs/Inactive
- Rollback releases the rows in the table, and releases the space from syslogs/Active to syslogs/Inactive
- Commit/Rollback are transaction control commands
- When "dump tran" (either manually or automatically by threshold sp) is executed, it writes the Inactive portion of syslogs to a named file (which you need for recovery) and clears syslogs/Inactive, releasing it to syslogs/Free. Unfortunately, this is known as "truncating the log", for historic reasons (think: truncate table, and syslogs is just another table), but "dumping the log" is probably more appropriate.
- With few exceptions (see below) truncating only happens when the log is dumped.
- "dump tran" is a db level administrative (space management) command; it is quite independent of: "update/insert/delete" (which uses up the log) and "commit/rollback" (which releases it).
- Whether another user "sees" the data or not depends on the updating user committing the tran, NOT on any user dumping (truncating) the log. Do not try to read the log, it is for internal use only, just select from the table (and suffer the contention until transactions are complete).
- 1000 rows/transactions are not transactions, they are obese and resource-hogging batches, that will cause deadlocks and log file problems.Ensure your log is on a separate segment (to the data).
- Whether the users are separated by distance or sitting next to each other makes no difference: they are operating on (contentious (locked) rows in) a single table in a db in a server.
- You cannot keep extending syslogs; you are much better off designing smaller transactions.
- set abort_tran_on_log_full db_option, it eliminates the stress of trying to clear a choked syslogs, with the same effect as you would eventually, if you did so manually.
If, and only if, it is a development db, you can set the db_option "trunc_log_on_chkpt", which allows you to avoid manual or threshold "dump tran <dbname> to <file>", but the db is then unrecoverable.
For db recoverability, you need:
a. One database dump file (created with "dump database <db> to <file>")Note, any "lost" log dumps (truncation of syslogs without dumping: dump tran with truncate_only; dump tran with no_log; trun_log_on_chkpt db_option set) or any non-logged command (RTFM), deems the db unrecoverable, terminates the series (b), and you have to do (a) again.
b. plus the series of transaction dumps (created with dump tran <db> to <file>"), manually or by threshold, since (a)