Search code examples
sqlsybase

Can I perform a dump tran mid-transaction? Also, effects of using delay on log?

Good day,

Two questions: A) If I have something like this:

COMPLEX QUERY
WAIT FOR LOG TO FREE UP (DELAY)
COMPLEX QUERY

Would this actually work? Or would the log segment of tempdb remain just as full, due to still holding on to the log of the first query.

B) In the situation above, is it possible to have the middle query perform a dump tran with truncate_only ?

(It's a very long chain of various queries that are run together. They don't change anything in the databases and I don't care to even keep the logs if I don't have to.)

The reason for the chain is because I need the same two temp tables, and a whole bunch of variables, for various queries in the chain (Some of them for all of the queries). To simply the usage of the query chain by a user with VERY limited SQL knowledge, I collect very simple information at the beginning of the long script, retrieve the rest automatically, and then use it through out the script

I doubt either of these would work, but I thought I may as well ask.

Sybase versions 15.7 and 12 (12.? I don't remember)

Thanks, Ziv.

Per my understanding of @michael-gardner 's answers this is what I plan:

FIRST TEMP TABLES CREATION
MODIFYING OPERATIONS ON FIRST TABLES
COMMIT
QUERY1: CREATE TEMP TABLE OF THIS QUERY 
QUERY1: MODIFYING OPERATIONS ON TABLE
QUERY1: SELECT
COMMIT
(REPEAT)
DROP FIRST TABLES (end of script)

I read that 'select into' is not written to the log, so I'm creating the table with a create (I have to do it this way due to other reasons), and use select into existing table for initial population. (temp tables) Once done with the table, I drop it, then 'commit'. At various points in the chain I check the log segment of tempdb, if it's <70% (normally at >98%), I use a goto to reach the end of the script where I drop the last temp tables and the script ends. (So no need for a manual 'commit' here)

I misunderstood the whole "on commit preserve rows" thing, that's solely on IQ, and I'm on ASE.

Solution

  • Dumping the log mid-transaction won't have any affect on the amount of log space. The Sybase log marker will only move if there is a commit (or rollback), AND if there isn't an older open transaction (which can be found in syslogshold)

    There are a couple of different ways you can approach solving the issue:

    • Add log space to tempdb.

    This would require no changes to your code, and is not very difficult. It's even possible that tempdb is not properly sized for the sytem, and the extra log space would be useful to other applications utilizing tempdb.

    • Rework your script to add a commit at the beginning, and query only for the later transactions.

    This would accomplish a couple of things. The commit at the beginning would move the log marker forward, which would allow the log dump to reclaim space. Then since the rest of your queries are only reads, there shouldn't be any transaction space associate with them. Remember the transaction log only stores information on Insert/Update/Delete, not Reads.

    Int the example you listed above, the users details could be stored and committed to the database, then the rest of the queries would just be select statements using those details for the variables, then a final transaction would cleanup the table. In this scenario the log is only held for the first transaction, and the last transaction..but the queries in the middle would not fill the log.

    Without knowing more about the DB configuration or query details it's hard to get much more detailed.