User names in translated log file

[BudDurland]

We are trying to solve a mystery regarding a bad update that keeps happening in our SQL/Anywhere 16 database nad seems to be hard to reproduce. I can run dbtran and find the errant UPDATE query but what I really need is the user name that issued the command. I know I have the ability to filter out or include users when running dbtran, but I have literally dozen of possible suspects. Doing it one at a time is impractical. I read some documentation about turning on auditing, but I'm not sure it will give me what I'm looking for.

[johnsmirnios · Accepted solution]

When you see something like --UPDATE-1029-0000993526 the number '1029' is the connection identifier. Search backwards until you see the most recent 'CONNECT' entry with the same connection id: --CONNECT-1029-0000993365-DBA-3616-08-03 12:42

So this update was done by DBA.

If the transaction spans different transaction logs, I think you get a 'USER' entry rather than a 'CONNECT' entry.

[BudDurland · Accepted solution]

Thanks to both John and Nick; knowing what was the connection id was the missing element.

[Former Member]

In your transaction log translation the update will look something like this entry

   --UPDATE-1012-logoffset
   UPDATE owner.table_name
      SET . . . VALUES ( . . . ) WHERE  . . . 
   go

where the connection number you have ( -1012- in the above) can be match to the previous connect operation which will look something like this log entry

--CONNECT-1012-logoffset-DBA-2016-07-31 11:27

where the login account follow the logoffset.

If this connection has been around from some time and you have been running incremental backups around this time you may have to chain back through a few of the renamed logs to find the connection but it should alway be logged.

All of this is possible from the transaction log translation you are currently using. You don't need to add auditing for this to work.