Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: 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?
    MCSD .NET, SCJP, SCJWD

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    49

    Here is the sp_who2 result

    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
    MCSD .NET, SCJP, SCJWD

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Which process did you try to kill?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  6. #6
    Join Date
    Apr 2004
    Posts
    49
    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...
    MCSD .NET, SCJP, SCJWD

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •