SAP HANA – problem with terminating sessions
In last time I had problem with long running SAP HANA session, I couldn’t stop this operation. I saw note: 2092196 – How-To: Terminating Sessions in SAP HANA. I used all commands from this note but I still have problem with terminating session. Session was still active, although:
servername> hdbcons 'transaction d 18'
SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'SID', instance 'XX'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 25454)
[OK]--
## Start command at: 2022-01-03 11:28:16.711
[OK]## Finish command at: 2022-01-03 11:28:16.711 command took: 16.000 usec
--
[EXIT]--
[BYE]
servername> hdbcons 'connection c 360391'
SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'SID', instance 'XX'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 25454)
[OK]--
## Start command at: 2022-01-03 11:20:46.037
Successfully cancelled connection 360391
[OK]## Finish command at: 2022-01-03 11:20:46.122 command took: 85.767 msec
--
[EXIT]--
[BYE]
servername> hdbcons 'connection d 360391'
SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'SID', instance 'XX'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 25454)
[OK]--
## Start command at: 2022-01-03 11:17:49.976
Successfully disconnected connection 360391
[OK]## Finish command at: 2022-01-03 11:17:50.130 command took: 154.660 msec
--
[EXIT]--
[BYE]
Of course the same steps I tried did in HANA Studio – from SQL level.
In HANA Studio navigate to - "Administration" -> "Performance" -> "Sessions" -> right-click on session -> "Cancel session".
OR
ALTER SYSTEM CANCEL SESSION '<connection_id>'
The same situations. Process was still alive 🙁
What’s next
I decided to write to OSS and ask them questions. Below You find anwers.
- Where was the problem?
Only active operations within transactions are cancelled. An active transaction without a currently active operation is left untouched.
2. Why any commands didn’t terminate the current database request and transaction of a session?
Cancel session only rolls back the transaction when a database request is currently active. In case of an idle connection no transaction rollback is triggered.A session is a combination of connection,thread,transaction, SQL statement.
3. Is any other possibilities to stop the process?
SAP Note – ##2092196 provides overview of the settings and commands useful to terminate long running sessions.
4. Maybe only HANA restart can help in this situations?
There is no guaranteed way to terminate a running session recommended by SAP.When SAP HANA actively checks for termination requests,the termination will succeed.