Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    15

    Question Unanswered: how to clear a transaction log

    I use Sybase SQL Advantage as my interface to Sybase database, i cannot execute queries or any other stored procedure in a specific database. the error is as follows

    " The transaction log in the database <database name > is almost full. Your transaction is suspended until space is made available in the log. "

    Pls suggest me how to clear the transaction logs of a database or how to increase the size of the transaction log of a database.


    With thanks and regards
    Sathya V

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    drump tran <db-name> to ...

  3. #3
    Join Date
    Oct 2004
    Location
    India
    Posts
    11
    You can clear the transaction log by using the following command

    Dump tran < db_name > to < file_name >
    OR
    Dump tran < db_name > with truncate_only
    OR
    Dump tran < db_name > with no_log

    Please read the sybase documents for more details.

    Note : Dump tran will not work, if your database option is set
    to "truncate log on checkpoint".

    if still log is not cleared then, either you can kill the process
    or can abort the transaction log by using lct_admin command.

    If you want to increase the size of the transaction log of a database, you can
    increase it by using:

    alter database < db_name > log on < logical_device_name > = '100M'

    Regards,
    Sanoj Nair

  4. #4
    Join Date
    Jan 2005
    Posts
    2
    Hi Sanoj Nair,

    On your reply you noted that "Dump tran won't work if database option is set
    to "truncate log on checkpoint". ", if so how can one dump/clear a transaction log? and does it apply to tempdb log?

    I am running into the same problem, and I know for a fact the database option for "truncate log on checkpoint" is enable, however if I try to dump transaction log with either of the two options below I get an error about a not being able to dump transaction log due to a process still running. When I try to find out what the process number is via running sp_who I get the original error, so how can I find the process id to kill it? or is there a way around on how to dump a tempdb transaction log without having to kill process running? Any help will be greatly appreciated.

    Thanks.

    Dump tran < db_name > with truncate_only
    OR
    Dump tran < db_name > with no_log

  5. #5
    Join Date
    Oct 2004
    Location
    India
    Posts
    11
    Hello Sathya,

    On your reply you noted that "Dump tran won't work if database option is set
    to "truncate log on checkpoint". ", if so how can one dump/clear a transaction log?

    Ans: If "turncate log on checkpoint" option is "ON" then server automaticatlly truncates the log
    when it does the checkpoint. So, Dump tran will not work in this case.


    Does it apply to tempdb log?
    Ans: Yes, it apply to tempdb log also.

    I am running into the same problem, and I know for a fact the database option for "truncate log on checkpoint" is enable, however if I try to dump transaction log with either of the two options below I get an error about a not being able to dump transaction log due to a process still running. When I try to find out what the process number is via running sp_who I get the original error, so how can I find the process id to kill it? or is there a way around on how to dump a tempdb transaction log without having to kill process running?


    Ans: If you are unable to run the sp_who command, that means your tempdb is full.
    In this case sometimes, even you won't be able to query the system tables.
    If you can, then try to run the following query :

    select * from syslogshold

    you will get the process which is holding the logs since long.
    This is the only method by which you can find out the culprit process, when the tempdb is full.

    Now if you don't know the process and you want to abort all the open transactions in the database then
    use lct_admin command.

    Last option is to increase the log space.

    Regards,
    Sanoj Nair

  6. #6
    Join Date
    Jan 2005
    Posts
    2
    Hi Sathya,

    First and foremost thank you for your promptly clear detailed answers. You have clarified my confusion.

    One more question if you don't mind?, lately we have been running into this issue where some of our queries have been hanging, in our development environment, and our inability to dump the transaction log has made us re-start the database server everytime something like this happens. This of course is not ideal, because we have alot of individual databases that reside on the same database server and are affected everytime we do this. It sounds to me that if we were to set the "abort tran on log full" 'ON' we could in a sense resolve this issue for good. Would you agree? any thoughts would be greatly appreciated.

    Thanks.

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    How large is your log segment compared to the rest of your DB? Is it on its own device (e.g. are you mixed log and data)? What types of operations are causing the log jams (deletes, updates?)
    Thanks,

    Matt

  8. #8
    Join Date
    Oct 2004
    Location
    India
    Posts
    11
    Hi vgclos,

    It's a better idea to set the "abort tran on log full" 'ON', provided the server should be a development server.
    I will not recommend you to set this option in production environment.

    Regards,
    Sanoj Nair

  9. #9
    Join Date
    Dec 2004
    Posts
    25
    Question : Why can't you dump the transaction log if the 'trunc log on checkpoint' is enabled ?
    My understanding has been that this option is used to clear the committed transactions in log everytime a checkpoint occured.
    Dumping a log manually (not recommended on a replicated database) clears the log immediately rather than waiting for the checkpoint to occur.
    Both should be mutually exclusive.

    Suggestion : If you log segment fills up regularly, you should use the sp_thresholdaction and define what needs to be done when the log is almost full.

  10. #10
    Join Date
    Oct 2004
    Location
    India
    Posts
    11

    Smile

    Sybase will not allow you truncate the log, if the 'trunc log on checkpoint' is enabled. It is because the server automatically truncates the log after the checkpoint. So, it won't allow you to truncate further manually.

    Just try once.

    It's a good idea to use the sp_thresholdaction and define what needs to be done when the log is almost full, but in this case also the above points will have to be taken into consideration.

  11. #11
    Join Date
    Dec 2004
    Posts
    25
    My database options : select into/bulkcopy/pllsort, trunc log on chkpt

    log only free kbytes = 60662

    dump tran <dbname> with truncate_only

    log only free kbytes = 61198

    No errors, no problems. I have always used this on all our servers without issue.

Posting Permissions

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