Table Locking & Killed/Roll-back Session Issue

Question

Table Locking & Killed/Roll-back Session Issue

lockered

SSC Veteran

Points: 211
More actions
June 2, 2015 at 1:05 pm

#304564

I have an SSIS Package that runs an SQL command on a DB2 linked server and inserts the resulting data into the SQL Server table using the following :
insert into StagingTableForDB2Data
execute(@cmd) at LinkDB2db
From time to time the Job will just hang and we end up stopping it.
What that leaves us with a lock on the table StagingTableForDB2Data meaning we cant rerun it. The only way we have found to get around this is to restart the server.
Here are details on the session that is causing the lock:
If we leave it, it will just sit there for days and never rollback so we are left with this lock.
I believe it is waiting for the DB2 to respond and to that end I know that on peak times non priority queries on the DB2 server are killed. For some reason this doesn't come back to sql server and it just waits.
Want to know how we can release without having to restart the server and how can make it more "gracefull"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

This is a tough one. You have to find the connection on the DB2 side of the house and kill it before the SQL Server connection will rollback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lockered SSC Veteran Points: 211 More actions · Answer 2

So checked with the DB2 DBA's and they see no session but believe it was killed on their side when due to high loads on the server.

Given it was killed I would expect the process on the SQL Server side to either fail or be killed and rollback.

Question now is for the stuck process on the SQL Server side how can I get rid of it without having to restart the server ?

henri2398 SSC Journeyman Points: 86 More actions · Answer 3

Well, I don't have enough knowledge but I hope this may help you - DB2 Server Table Space Locked