Post reply

Check for SQL locks and kill then end the locking .exe

  • May 14, 2009 at 2:54 am

    #204793

    Hi all

    i have a trigger that pulls data, then does an update on the table its pulling from.

    no locks allows me to select the data etc without locking issues, but when i want to update the table and it is locked i am simply timing out.

    so before i execute the trigger i would like a script that would check for locks in the table in question - and if it is locked i would want to kill all cr.exe process's running on the server

    can anyone help?

  • May 14, 2009 at 4:19 am

    #994355

    Hi,

    Try the

    EXEC sp_lock

    ARUN SAS

  • May 14, 2009 at 4:39 am

    #994360

    i already know what is locking

  • May 14, 2009 at 8:41 am

    #994590

    for anyone whos interested...

    declare @cmd_t table (id int not null identity(1,1) primary key, cmd nvarchar(200))

    declare @wait_time int

    set @wait_time=60000

    insert into @cmd_t(cmd)

    select distinct 'kill '+convert(nvarchar,spid) from sysprocesses where spid in (

    select blocked from sysprocesses where blocked!=0 and waittime>=@wait_time) and blocked=0

    declare @i int,@cmd nvarchar(200)

    while exists(select top 1 null from @cmd_t)

    begin

    select @i=id,@cmd=cmd from @cmd_t

    if @@rowcount<=0 break;

    delete from @cmd_t where id=@i

    if @@rowcount<=0 break;

    exec(@cmd)

    end

  • May 14, 2009 at 8:58 am

    #994604

    You shouldn't have long locks that prevent updates. An update should granularly lock a row or page, and only if it's a large update would it get the table.

    If that's happening, your solution might help with the issue, but you have architectural problems. You might reexamine the way your application is working.

Viewing 5 posts - 1 through 5 (of 5 total)

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