Killing locks & session PROBLEM | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Killing locks & session PROBLEM

Hi,
I have Windows 2000 Advanced Server with SP4 and SQL Server 2000 Enterprise Edition with SP3a. I have SQL Cluster (Active-Passive) environment. This server box is used for JDE software.
I recently had a very strange problem that through the JDE application, developers tried to delete some record from a table but it hang up all the system so they ended the application from Windows Task Manager. But the intersting thing is that the lock from SQL Server didn’t get released for that deletion. When I see in the Current Activities it shows ‘Transaction in progress’ and in another column it shows ‘rolling back’.
When I tried to kill this process then it doesn’t return any error but does not kill that process. I tried to kill that process through Query Analyzer and ther it shows ‘estimated time: 16 seconds’.
I left it as it is for a day but nothing happened. I was unable to do any thing on that particular table(select, insert, update, delete, etc) but rest is working very fine without any problem. Then I had to restart the services of SQL Server for that and then everything was fine.
So my questions are:
1) I am curious about this lock…what could be the possible reason for that? Why that lock could not be killed.
2) How can I get all the locks released on a particular table (users are working on rest of the tables)?
3) How can I get all the locks released on a particular Database(users are working on rest of the databases)?
Regards,
Manoj Verma
DBA
1) If SQL is Rolling Back you can’t kill process. And this is rigths in order to not corrupt data or don’t loose information.
2) I think via programing, I mean code with NO LOCK if it is possible. But, try to run Monitor Performance and Profiler, because may be you can aboive lock with tunning.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Hi,
Thanks for your reply.
Yea I was able to select the records from that table with (nolock) but nothing else could be done at that moment. And I waited for about 24 hours for it to get completed but it didn’t respond at all. And there were only 1600 records in that table and only 15 small columns were there in that table. So I could not find any other way but to restart the server. And by the way, my rest 2 questions are still there: 2) How can I get all the locks released on a particular table (users are working on rest of the tables)?
3) How can I get all the locks released on a particular Database(users are working on rest of the databases)?
Regards,
Manoj Verma
DBA
]]>