Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: Sybase transaction log overflowing into database space when log space is full

    Does the Sybase transaction log overflow into database space when the log space is full? I have been using the following code for years, and it sometimes shows that the log is more that 100% full. Example output:

    Total log size is 1000 Mb.
    There are 1030 Mb used and -30 Mb free. (103.067578% full)

    CREATE PROC sp_logstat @program_user char(3) = NULL
    as
    set nocount on
    set arithabort numeric_truncation off /* needed for system 10 only */

    declare @db_size int
    declare @res_pgs int, @res_bytes int
    declare @db_name varchar(30), @free_pgs int, @free_bytes int
    declare @free_mb float, @res_mb float, @perc_full float
    declare @dbsize_mb int

    select @db_size = sum(size)
    from master..sysusages
    where dbid = (db_id(db_name())) and (segmap = 4)

    select distinct
    @res_pgs = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
    from sysindexes where segment = 2

    select @db_name = db_name()
    select @free_pgs = @db_size - @res_pgs
    /* select @res_bytes = @res_pgs*2048 */
    /* select @free_bytes = @free_pgs*2048 */
    /* select @res_mb = @res_bytes / 1024000 */
    /* select @free_mb = @free_bytes / 1024000 */
    /* select @dbsize_mb = @db_size * .002 */
    select @res_mb = @res_pgs / 512
    select @free_mb = @free_pgs / 512
    select @dbsize_mb = @db_size / 512

    select @perc_full = ((@res_pgs*1.00) / (@db_size*1.00)) * 100

    if @program_user != "alt"
    begin
    print "The current database is %1!.", @db_name
    print "Total log size is %1! Mb.", @dbsize_mb
    print "There are %1! Mb used and %2! Mb free. (%3!%% full)",
    @res_mb, @free_mb, @perc_full
    end
    else
    print "%1!:%2!:%3!:%4!:%5!",
    @db_name, @dbsize_mb, @res_mb, @free_mb, @perc_full
    Last edited by tomstone_98; 01-14-05 at 10:24. Reason: Added question mark icon

  2. #2
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Log does not 'overflow'

    1 Under heavy use, it is normal for the log to be more than 100% full, as these values are very current runtime server values, and are not entirely meaningful (only indicative) when selected and used in a query. The difference (103% - 100%) indicates the currently active pages. No comment on your sp code.

    2 Whether or not the log spills into data space is a separate issue. If you ensure that you have separate log/data devices and log/data space allocations, then they are separate, and the log does not flow into data space. Note default devices, and the correct create database commands. If the create database commands do not specify a separate log, then the log will run into (and fill up) the data space (but then there is only one data+log mixed space).

    3 There is no "overflow" space for the log, it fills up (to 103% or 104% based on virtual values) and the activity on the database is suspended, until the log is dumped or truncated (by threshhold or db_option or manual command).
    Last edited by DerekA; 01-29-05 at 20:59.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    Thank you for your reply.

    2 questions.

    1. Concerning "and the activity on the database is suspended, until the log is dumped or truncated" ... We have a monthly deletion process that basically does:
    for i = 1 to 15
    begin tran
    delete 5000 rows from table i
    commit tran

    This process runs every minute and the %full keeps increasing even after 100% (I've seen it get up to 170%) ... also ... other processes continue to hit the database ... hence it appears that activity on the database is not suspended. I suppose the "currently active pages" keeps growing and growing ?

    2. What do you mean by "but then there is only one data+log mixed space"?

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    More on How Log does not "overflow"

    0 ... delete 5000 rows in one tran ...
    Come on, mate, that's not a transaction, that's a BATCH. It is an exceeding unfriendly thing to do if you have other users on the server. Do whatever it takes to reduce the count to 100 or 500 or (absolute max) 1000 within one transaction if the code runs every minute and while other users are hitting the db. If all you are doing is deleting, and every minute, then the count should not matter. You can also run every second. You will gain some friends.

    A better solution is to modify the code segment that marks or deems the rows deletable, to delete the rows. They would be in the cache and updated and logged at that very moment and generally the transaction size would be much smaller.
    1 ... hence it appears that activity on the database is not suspended. I suppose the "currently active pages" keeps growing and growing ?
    Well, no. What is more likely is any or all of:
    (a) you sp code may have bugs in reporting the % full value (I have never seen it go over 105% on 20+ production severs and some custs have very bad code; I will not check your sp code as I have my own code which uses a different approach and is very accurate, even for 5gb logs)
    (b) your DBA has expanded the log size to massive proprotions to deal with the massive "transactions" (eg. mass deletes above)
    (c) you have mixed data+log space, so there is no separate log; there is enough free space in the mixed data+lob space; and your sp code is incorrect (as it does not take this into account)
    (d) you either have the truncate_log_on_checkpoint db_option set (which means you have no recovery) or a reasonable threshhold sp in place, that dumps the log before it gets full.
    (e) then there is the possibility that you actually have MicroShaft SQL Server (pitiful code) and you are in the wrong forum, in the hope that the products are somehow "equal".
    2 What do you mean by "but then there is only one data+log mixed space"?
    You can have one of only two conditions:
    - separate data and log space (using the "... LOG ON <device>=<size>" parameter in the CREATE DATABASE command, <size> defines your finite log size)
    - mixed data+log in the one database space (not using the "... LOG ON" parameter, the device contains both in the <size> and there is no separate finite log (or data) size).

    Hence: If the create database commands do not specify separate data and log space (therefore the one space contains mixed data+log), then the log [or data] will run into, contend with, and possibly fill up, the one mixed data+log space and block the data [or log] from expanding. The log can fill a mixed data+log space, or a separate log space, but not a separate data space.
    Last edited by DerekA; 02-02-05 at 12:04.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    If you are deleting all rows, simply do TRUNCATE TABLE foo. Much faster, minimally logged, better for the environment, promotes world peace, etc.
    Thanks,

    Matt

  6. #6
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    Thanks for everyones help.

    The bottom line is that I suspect that the sproc is not totally correct or it is misleading. If I pause the iteration of deletes (after seeing the "100+%" full message) the log eventually clears or decreases in size. Hence, maybe it would help to do checkpoints more frequently?

    We do have separate devices for log and data ...

    We keep 3 months of data in the table (the monthly delete removes the data for the oldest month) ... hence I can't do a truncate table ... (we also have Sybase replication rep'ing data from this database -- so a truncate table is not allowed)

  7. #7
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    The bottom line is that I suspect that the sproc is not totally correct or it is misleading.
    Yup.
    If I pause the iteration of deletes (after seeing the "100+%" full message) the log eventually clears or decreases in size.
    That proves certain things (reinforces my point and provides the basis for what I propose below).
    Hence, maybe it would help to do checkpoints more frequently?
    Well, sort of. But you will need sa permission, therefore you will have to run the sp as sa. Even if you do checkpoints, all you will be doing is interfering with the runtime mix of tasks enough to arrest the inevitable filling up of the log. Not something tabI would recommend, I would rather you get your code working with decent transaction sizes (much better method of avoiding filling up the log) under the correct user and without manual intervention.

    If the speed on the batch delete is not an issue, you can get a similar effect by throwing a WAITFOR in the delete loop.
    We do have separate devices for log and data
    Good.
    ... hence I can't do a truncate table ... (we also have Sybase replication rep'ing data from this database -- so a truncate table is not allowed)
    Understood.
    We keep 3 months of data in the table (the monthly delete removes the data for the oldest month)
    Actually, you always have 91 to 120 days worth of data. Then once a month you delete one fourth of the data (the 91 to 120 bit), retaining 90 days worth. Consider:
    - Every Day:
    --- delete the oldest days (91 days old) data, thereby always retaining 90 days worth
    (or the like: the idea is to spread the months load across 30 days)

    In any case, absolutely ensure these two:

    1. that the columns in the WHERE clause in the DELETE are indexed and (showplan) the update mode is direct, but do not be surprised if it is table scan not index scan.

    2. that you have a threshhold_sp on the logsegment that dumps the log. Without considering the monthly delete, this should be set at (eg) 70% and known to be in good working order. Then for the monthly delete:
    - add a threshhold_sp at (eg) 40%
    - do the delete
    - drop the threshhold_sp at 40%
    This is functionally equivalent to checkpointing more frequently.

    Note, a "good" threshhold_sp has to ensure [upon being triggered twice or under LCT] that a second log dump does not get triggered until any currently-running log dump has completed. Otherwise, you will have to do annoying, fiddly things like drop the existing threshhold_sp at 70% when you add one at 40% and reverse that later.

    Later...
    If you cannot do a daily delete and therefore you have to delete 25% of the data once a month, get the above bedded down really well (ie. smaller transactions and proper threshhold_sps so that your log does not fill up during the monthly delete). Then, and only then, you can enhance the process:
    - remove the WAITFORs if you put them in
    - Depending on no. of CPUs, load (at the time the DELETE batch job is running), etc., split the DELETE job into two [or four] (by virtue of key values) and run two [or four] DELETE jobs concurrently. Eg. for two jobs, the second job starts at [add this to the WHERE clause] AND key > max(key) / 2.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  8. #8
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83

    lct_admin is the answer

    I think the main problem here is that using "curunreservedpgs" or other columns in the sysusages table to determine log segment usage is no longer reliable since ASE 12.0.

    Look at the "installmaster" script in $SYBASE/$SYBASE_ASE/scripts directory to see how "sp_helpdb" does it. It uses the builtin "lct_admin" to determine the number of pages free in the log. Change your script to use this function instead of the "sysusages" table. BTW, this is the same mechanism that the threshold process uses to determine when to fire.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •