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
Wednesday, March 28, 2012
How to remove lock?
Labels:
activity-locks,
application,
database,
enterprise,
lock,
locks,
management-current,
manager,
microsoft,
mysql,
oracle,
preventing,
process,
server,
sql,
sql-server
Subscribe to:
Post Comments (Atom)
 
No comments:
Post a Comment