locked
How to clear a lock RRS feed

  • Question

  • Database has a lock that is preventing users from using it. How do I clear this lock?
    Wednesday, December 18, 2013 7:41 PM

Answers

  • Database has a lock that is preventing users from using it. How do I clear this lock?

    You cannot clear the lock unless you kill the transaction locking ( not recommended) or the transaction itself commits or rollback.

    Locking is internal mechanism by which RDBMS tried to achieve isolation between two concurrent transaction trying to modify same row/page/table.

    So either you have to wait for lock to release or for transaction to commit.If you are facing long blocks and want to resolve the issue i suggest you read below MS article

    http://support.microsoft.com/kb/224453

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Thursday, December 19, 2013 12:19 PM
    • Marked as answer by ghw123 Thursday, December 19, 2013 10:47 PM
    Thursday, December 19, 2013 7:15 AM

All replies

  • Hi,

    What is the exact error message?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, December 18, 2013 7:50 PM
  • Sounds like your company needs a DBA.

    The information you supplied is insufficient.

    Can you post some screen images? Error messages?


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, December 18, 2013 7:52 PM
  • Have Adam's great stored procedure to see who/how/by whom is locked

    Who Is Active? v10.00 (2010-10-21)
    (C) 2007-2010, Adam Machanic

    Feedback: mailto:amachanic@gmail.com
    Updates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
    "Beta" Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx

    License: 
    Who is Active? is free to download and use for personal, educational, and internal 
    corporate purposes, provided that this header is preserved. Redistribution or sale 
    of Who is Active?, in whole or in part, is prohibited without the author's express 
    written consent.

    Usage

    /*BLOCKING*/
    EXEC dbo.sp_WhoIsActive @find_block_leaders =1,
    @output_column_list ='[blocked_session_count][blocking_session_id][session_id][CPU][status][wait_info]
                          [database_name][sql_text][host_name][open_tran_count]',
    @sort_order='[blocked_session_count]DESC'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sofiya Li Thursday, December 19, 2013 12:19 PM
    Thursday, December 19, 2013 6:11 AM
    Answerer
  • Database has a lock that is preventing users from using it. How do I clear this lock?

    You cannot clear the lock unless you kill the transaction locking ( not recommended) or the transaction itself commits or rollback.

    Locking is internal mechanism by which RDBMS tried to achieve isolation between two concurrent transaction trying to modify same row/page/table.

    So either you have to wait for lock to release or for transaction to commit.If you are facing long blocks and want to resolve the issue i suggest you read below MS article

    http://support.microsoft.com/kb/224453

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Thursday, December 19, 2013 12:19 PM
    • Marked as answer by ghw123 Thursday, December 19, 2013 10:47 PM
    Thursday, December 19, 2013 7:15 AM
  • Thanks
    Thursday, December 19, 2013 10:47 PM