Post reply

Inactive process holding locks on table

  • March 25, 2009 at 2:14 am

    #198348

    Hi guys,

    I have a process that runs every 3 minutes - at least.

    When I run sp_who2 'active' I see processes blocked. Tracking down the culprit I find out that it's an inactive process causing the blocking.

    DBCC opentran returns no active/open transactions.

    By using DBCC inputbuffer(spid) I get the command: (@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 bit)EXEC UpdateTable1 @P0,@P1,@P2,@P3.

    When running sp_lock I get the following:

    spiddbidObjIdIndIdTypeResourceModeStatus

    157 13927355114 1KEY(86006a1d0d15) XGRANT

    157 13927355114 1PAG1:2957261 IXGRANT

    157 13927355114 0TAB IXGRANT

    Where the object is the table in question.

    Table, Index, Constraint & Stored Procedure definitions are attached. I've included sample data as well.

    One or two things on the table in question:

    - I has roughly 2 200 rows.

    - 1 index only (Clusted - One column)

    - Being used in transactional replication (Publisher)

    Now, to my question(s):

    Why is an inactive process creating locks?

    There is no explicit BEGIN TRAN / COMMIT TRAN in the sp, surely it doesn't need it?

    What can/should I do to investigate further?

    EDIT:

    Don't know if server info is needed but:

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on

    Windows NT 6.0 (Build 6001: Service Pack 1) )

    32GB RAM (26GB to this instance)

    2x Intel Xeon E5450 3.00Ghz CPU's

    Some IBM SAN (Not sure of model)

    MDF & LDF on seperate, dedicated drives

    Thanks for your help guys!

  • March 25, 2009 at 4:48 am

    #965883

    Hi guys,

    Do you have any ideas?

  • March 25, 2009 at 7:25 am

    #965967

    The basic code doesn't cause locking all by itself. So, the problem must lie elsewhere. Are you getting errors? Does the code that's calling the proc have a transcation wrapper?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 25, 2009 at 8:25 am

    #966004

    Hi Grant,

    Thanks for the reply.

    I don't know too much (or anything) about C# code but below is what I'm told is how it's executed:

    try

    {

    int res = new SqlCommand("UpdateTable1" + messengerid + ",'" + Context.Request.ServerVariables["REMOTE_HOST"] + "','" + version + ".webep" + "',0", databaseserver).ExecuteNonQuery();

    }

    catch (Exception ex2)

    {

    sendMail("Could not update pru_LastConnected", ex2.Message);

    }

    e = "OK";

    databaseserver.Close();

    Does that make any sense?

    This process is in sleeping state but still holds locks... Can't get my head around it :crazy:

  • March 25, 2009 at 11:40 pm

    #966431

    Is there anyting else you can think of?

  • March 26, 2009 at 9:02 am

    #966750

    This week there was a process sitting in sleeping status AWAITING COMMAND that blocked some other processes, and cascading effects was about 100 processes blocked

    Killing the process resolved the issues in 10 seconds, but I still don't know what caused it (happened only once)

    Usually it's a valid query blocking others, not an inactive one @@

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • March 27, 2009 at 12:09 am

    #967213

    Hi Jerry,

    Yes this is a 'valid' query - But for the size of the table & the complexity of the transaction, it shouldn't hold locks for as long as it currently does.

    We're busy investigating the box this query is being run from, it looks like there are some memory issues there - We'll see what that brings.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply