Sybase ASE Transaction log full

[ps_vinay]

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

[former_member188958]

Why can't you execute any commands? What exactly happens when you try to select from master..syslogshold?

[former_member89972]

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

[ps_vinay]

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

[former_member89972]

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