Hi,

Is there a way to see the end of a connection in the transaction log files?

The begining of a connection is seen as:

--CONNECT-1028-0481325402-dba-2021-03-21 11:33

But How can track this connection to find what was the last transaction of this connection?

Simply looking for transactions with 1028 will not help since the same number 1028 could be allocated to a new connection once the last connection is finished

asked 12 May, 06:27

Baron's gravatar image

Baron
1.3k7399123
accept rate: 44%

Simply looking for transactions with 1028 will not help since the same number 1028 could be allocated to a new connection once the last connection is finished.

Note, connection IDs are only reused when either the database engine is restarted or the range of connection IDs has been fully consumed - i.e. for regular connections, 1 billion connections have been made since the last server start. Cf. that FAQ.

(12 May, 09:28) Volker Barth

Disconnects are not always logged such as if the server is restarted without a clean shutdown. The last transaction for a connection should be the last transaction that occurs before the next CONNECT for that connection ID (if there is one) or the end of the log(s) otherwise.

permanent link

answered 12 May, 08:47

John%20Smirnios's gravatar image

John Smirnios
10.8k392144
accept rate: 37%

But if the server was clean shutdown, then how it looks like a DISCONNECT in the transaction log files?

In our case there are usually several connections to DB at a moment, and the transactions are interlaced, so I don't think that your suggestion would work, or maybe I didn't get it.

(12 May, 09:16) Baron
Replies hidden

In my understanding of John's answer:

Every operation logged for connection ID 1028 starting from "CONNECT-1028" belongs to that actual connection until either the next "CONNECT-1028" entry is logged or the log end is reached.

(12 May, 09:34) Volker Barth

dbtran will not show the disconnects: they just generate a rollback but, of course, so will a real rollback.

No two connections that are active at the same time will have the same ID. Therefore, when you see a 'CONNECT' for a given ID it is a new connection and won't conflict with any others. Also note that dbtran output is, by default, ordered by the order in which transactions were committed and does not include transactions that were rolled back. In other words, it produces an equivalent serialization of the log contents (ie, it removes all of the interlacing of transactions).

(12 May, 09:36) John Smirnios

Yes, that too. :) Note that transactions can span logs so you want to look for the next CONNECT-1028 or the end of the log(s) that you are looking at.

dbtran can handle multiple files by putting them into a directory & using the -m switch.

Any transaction active at the time dbtran hits the end of the last file is assumed to have rolled back -- even if they continued on into the next log because dbtran cannot know if they continued unless it can see the next file.

(12 May, 10:05) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×154
×13

question asked: 12 May, 06:27

question was seen: 115 times

last updated: 12 May, 10:05