Skip to Content

Sybase ASE Transaction log full

Hi All,

One of our Sybase App DB transaction log got full. I was able to log on to Sybase server but couldn't execute any of the commands including "select" command.

So, how to find the culprit spid which is holding the log in this case.

Please explain the steps.

Thank you,
P S Vinay

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Apr 15 at 08:41 PM

    As Bret pointed out if you could connect, you should have seen the oldest/longest running transaction for a database by running "select * from master..syslogshold"

    If you could not connect/select, then you will not able to reconstruct the scenario.

    For long term :

    You need to have a monitoring process polling frequently to record entries from master..syslogshold.

    These should be stored away in tables/files for later analysis.

    Entries in ASE errorlog give you time frame when the issue started.

    Make sure that you find the real trouble maker and not the victim SPIDs that were terminated due to transction log getting full.

    HTH

    Avinash

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 08:34 AM

    select from master..syslogshold just hangs. Even sp_who hangs as well.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 16 at 02:47 PM

    Then most likely you were running out of tempdb data and/or log space.

    Both queries above depend on materialization of data in your tempdb.

    You may be able to tap into MDA tables with plain simple select without using any order by or group by or distinct clauses.

    As a best practice

    - have a separate tempdb_sa dedicated for "sa"/DBA work only and make that as "sa" login's default temporary database.

    - poll (e.g. every 1 to 5 minutes) for the contents of master..syslogshold and store for analysis

    HTH

    Avinash

    Add comment
    10|10000 characters needed characters exceeded