find connection end in Transaction Logs SQL17

[Baron]

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

[VolkerBarth]

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.

[johnsmirnios]

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.

[Baron]

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.

[VolkerBarth]

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.

[johnsmirnios]

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).

[johnsmirnios]

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.