You must Sign In to post a response.
  • Category: SQL Server

    Sql error Lock request time out period exceeded.

    Hi,

    i m getting below error message while executing procedure

    Msg 1222, Level 16, State 56, Line 4
    Lock request time out period exceeded.

    please help me resolve above errro
  • #767935
    Hi Chanti,

    It might happen when your job something get locked.
    So you need to first check which procedure or table got locked.


    DBCC opentran()


    You will get the detail of the active transaction, then from the SPID of the active transaction, get the detail about the SPID using following commands


    sp_who2 <SPID>
    sp_lock <SPID>


    For example, if SPID is 79 then execute the command as

    then,


    sp_who2 79
    sp_lock 79


    Now , you can kill that process using the following command

    KILL 79


    Now your issue will resolved.

    Thanks,
    Mani

  • #767941
    Hai Chanti,
    There could be many things which can cause this issue like:
    1. There could be there processes which are taking the processor's time. So you need to make sure that other processes are not taking time when running the Sql server. Try to restart the server and check again. It should be working fine.
    2. Yo u need to check the server memory because for each transaction, the SQL server try to allocate the memory and if there is no enough memory, the time will lapse and will throw the error message.
    3. If there is scenario where the transaction is happening in timely manner in that case also when the re is low bandwidth, this error can cause.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #767943
    it looks you have used transactions in your code, To prevent this error , make sure every BEGIN TRANSACTION has COMMIT The following will say successful but will leave uncommitted transactions, see below snippet

    BEGIN TRANSACTION
    BEGIN TRANSACTION
    <SQL_CODE?
    COMMIT

    Closing query windows with uncommitted transactions will prompt you to commit your transactions. This will generally resolve the Error 1222 message

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767952
    Hi,

    It's looks like the table has been locked by some one else, because of that only you got this type of error. I suggest you to first KILL the process or wait until the previous process has been completed.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


  • Sign In to post your comments