Sybase: Check for which objects you have transactions in the log

If you see that your transaction log is growing at an unexpected speed or are just curious which transactions are currently contained in the log, you can use the dbcc log operation:

dbcc traceon(3604)
dbcc log(mydbname, 0)

Replacing mydbname by the name of the database which transaction log you want to inspect. This will return quite some data. If you only want to see which objects (e.g. tables) are affected, you can use the following shell command:

cat <<EOT | isql -Usa -Pmysapassword | grep "objid=" | awk ' { print $1; }' | awk -F "=" '{ print $2; }' | sort | uniq -c | sort -n -r
dbcc traceon(3604)
dbcc log(mydbname, 0)

If isql is not in your PATH, write the full path instead (probably $SYBASE/OCS/bin/isql on a Linux box).

This will get the log data, extract the lines containing the object ID, then extract the object IDs, counting the number of time each one appears and sorting the list by decreasing number of occurences.

On my machine it returns something like this:

     10 2062679415
      6 20
      1 8

2062679415 being a test table I’ve filled for the purpose of this test.
8 is syslogs.
20 is sysanchors.

You can check what those returned object IDs are with the following statement run in your database:

1> select id, name from sysobjects where id in (8,20,2062679415)
2> go
 id          name

 ----------- --------
           8 syslogs
  2062679415 henri

Note that sysanchors (object ID 20) is not shown here because it is not a normal database table. It’s a per-database pseudo-catalog where row anchors used by system catalogs are stored.

Also keep in mind that checking the contents of the transaction log takes longer when it’s already full. So if you see that the log is filling at a constant rate, it might be a good idea to read from the log not too long after the last dump database or dump of the transaction log, so that it is faster (it will have an engine use 100% CPU for quite some time if it’s already full).

Leave a Reply

Your email address will not be published.