Wednesday, March 28, 2012

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

No comments:

Post a Comment