How to remove lock?

From Sql-server enterprise manager : Management-Current Activity-Locks/Process ID, I found several locks, which preventing some of application working property.
So, I want to remove locks manually.
How Can I do this?do sp_who2 and post the results...

Do mean you blocking? Locks are held until the work is done...which is what should be happening...

if it's a blocked spid, you'll probably have to kill the blocking spid...|||I tried to kill process of the locks(property, Kill Process) but, it doesn't work!

1 BACKGROUND sa . . NULL LAZY WRITER 563 0 06/15 13:44:44 1
2 BACKGROUND sa . . master SIGNAL HANDLER 31 0 06/15 13:44:44 2
3 sleeping sa . . NULL LOG WRITER 9719 0 06/15 13:44:44 3
4 BACKGROUND sa . . NULL LOCK MONITOR 141 0 06/15 13:44:44 4
5 BACKGROUND sa . . master TASK MANAGER 0 530 06/15 13:44:44 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 6
7 sleeping sa . . NULL CHECKPOINT SLEEP 94 282 06/15 13:44:44 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 8
9 BACKGROUND sa . . master TASK MANAGER 0 11 06/15 13:44:44 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 12
13 BACKGROUND sa . . master TASK MANAGER 0 20 06/15 13:44:44 13
14 BACKGROUND sa . . master TASK MANAGER 0 10 06/15 13:44:44 14
15 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 15
51 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 9892 228 06/21 13:05:58 MS SQLEM 51
52 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 52
53 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 53
54 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 0 0 06/21 13:07:43 prjMapDrive 54
55 RUNNABLE HRUser IT009 . TimeClock SELECT INTO 16 3 06/21 13:19:21 SQL Query Analyzer 55
56 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 63 0 06/21 12:58:38 56
57 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 93 29 06/17 09:57:05 SQLAgent - Generic Refresher 57
58 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 601318 1 06/21 13:19:26 SQLAgent - Alert Engine 58
63 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 219 0 06/21 12:59:05 63|||Which process did you try to kill?|||I do not see any blocking activity in this output. Are you sure there is a problem? Maybe I am not understanding what the problem is?|||I want to remove TimeClock DB related locks..
Since My application has problem with transaction on TimeClock DB, it sometimes begins transaction and doesn't commit or rollback. In this case problem occurs...
Then I have to remove locks related those transaction. but, I couldn't...
I just stopped Sql server and restarted.. But, I want to remove locks without restarting and affecting other DBs.

Thanks...|||Fix the application. Anytime an application has a timeout, it should automatically roll back the transaction. Killing processes is dangerous and should only be used in rare, extreme cases.|||If the application is leaving transactions open, without committing them, you can use dbcc opentran to identify the connection (SPID) that is at fault. This command must be run from the TimeClock database. With this information, you can convince the vendor/contractor/programmer to clean up their mess.sql

How to query lock contention

Is there a way to query SQL 2000 to find out about table/database lock
contention. I have an application that is getting locked out many times a
day. I can see using "sp_who2" that various spids are being blocked by
others, which in turn are blocked by another...
If I "kill" the top level spid the system frees. I would like to find out
what table that process was locking or holding onto that was causing the
other workstations to hang. Are there any statistics or tools that would
help me?
TIA
MarkTry the stored procedure sp_lock
--
Thanks
Ravi
"Mark Butler" wrote:

> Is there a way to query SQL 2000 to find out about table/database lock
> contention. I have an application that is getting locked out many times a
> day. I can see using "sp_who2" that various spids are being blocked by
> others, which in turn are blocked by another...
> If I "kill" the top level spid the system frees. I would like to find out
> what table that process was locking or holding onto that was causing the
> other workstations to hang. Are there any statistics or tools that would
> help me?
> TIA
> Mark
>
>

How to query lock contention

Is there a way to query SQL 2000 to find out about table/database lock
contention. I have an application that is getting locked out many times a
day. I can see using "sp_who2" that various spids are being blocked by
others, which in turn are blocked by another...
If I "kill" the top level spid the system frees. I would like to find out
what table that process was locking or holding onto that was causing the
other workstations to hang. Are there any statistics or tools that would
help me?
TIA
Mark
Try the stored procedure sp_lock
Thanks
Ravi
"Mark Butler" wrote:

> Is there a way to query SQL 2000 to find out about table/database lock
> contention. I have an application that is getting locked out many times a
> day. I can see using "sp_who2" that various spids are being blocked by
> others, which in turn are blocked by another...
> If I "kill" the top level spid the system frees. I would like to find out
> what table that process was locking or holding onto that was causing the
> other workstations to hang. Are there any statistics or tools that would
> help me?
> TIA
> Mark
>
>

How to query lock contention

Is there a way to query SQL 2000 to find out about table/database lock
contention. I have an application that is getting locked out many times a
day. I can see using "sp_who2" that various spids are being blocked by
others, which in turn are blocked by another...
If I "kill" the top level spid the system frees. I would like to find out
what table that process was locking or holding onto that was causing the
other workstations to hang. Are there any statistics or tools that would
help me?
TIA
MarkTry the stored procedure sp_lock
--
Thanks
Ravi
"Mark Butler" wrote:
> Is there a way to query SQL 2000 to find out about table/database lock
> contention. I have an application that is getting locked out many times a
> day. I can see using "sp_who2" that various spids are being blocked by
> others, which in turn are blocked by another...
> If I "kill" the top level spid the system frees. I would like to find out
> what table that process was locking or holding onto that was causing the
> other workstations to hang. Are there any statistics or tools that would
> help me?
> TIA
> Mark
>
>