Problem SQL Kill Session script on NAV 2009

kum_g7kum_g7 Posts: 12Member
edited 2011-07-06 in SQL General
I Develope Classic Navision. from 3.7 Until > 5.1 and alwaysUsed SQL scripts (COPY From Mibuso)
for Killing Session that have more Idle Time (It' s work on every version Below 2009)
but when use this Script on NAV 2009 Classic on SQL 2008 R2, I found the important problem that
1. When Script execute , Session are killed, but when I go to NAV Window that already killed and Open some form,
Session came back.
2. then if Customer have License 10 then they can use license more than 10.

I guess that problem from NAV 2009 have the difference architecture,
so any suggestion to fixed this problem, thank in advance


  • prototyperprototyper Posts: 70Member
    This seems very odd.
    We use NAV2009 on SQL 2008 R2 and often use Kill to terminate session and have not experienced any problem like yours.
    Perhaps there is something specific to your script. If you want to post it I can have a look see.
    Sleep on it... The best solutions come at 2am
  • pdjpdj Posts: 665Member
    NAV2009 doesn't need traceflag 4616 - could this missing traceflag cause problems for the script?
  • kum_g7kum_g7 Posts: 12Member
    thank you for all reply. This is SQL Script that I uesd. (Credit : COPY From mibuso)
    I can't find anything wrong, Please Help.

    USE [MACO_23052011]
    /****** Object: StoredProcedure [dbo].[sp_KillIdleSpids] Script Date: 07/06/2011 10:48:32 ******/

    ALTER procedure [dbo].[sp_KillIdleSpids]
    @sec int=Null,
    @NetSend bit=0,
    @msg char(160)=Null -- For 'Net Send' command, message string is limited to 160 char. --

    Name : sp_KillIdleSpids
    Description : Kills connections that have been
    inactive for @sec seconds.

    Usage : exec sp_KillIdleSpids <sec>, [<0;1>, <Message>]

    Author : Steve Jones -

    Modifications : Benoît Ourssaire -

    Input Params :
    @sec : int. defaults to 'Null', number of seconds for connection to be
    idle to kill it.

    @NetSend : int. defaults to '0', set to '1' for send message to computer (ie. user warning).

    @msg : int. defaults to 'Null', message to be displayed by the 'Net Send' command (if @NetSend enable)
    Note : accents are improperly displayed by 'Net Send' command.

    Output Params :

    Return : - 0, no error.
    - Raises error if no parameters sent in.

    Results :

    Locals :

    Modifications :

    - Add a condition in 'where' clause to select only program name with *Navision* string.
    - Add a 'Net Send' to warn users.
    - Change 'select @cmd=...' by 'exec(...)'
    - Add two variables to manage the 'Net Send' command.
    - Add code to manage the 'Net Send' command.


    declare @err int,
    @spid int,
    @cmd char(255),
    @hostname char(255)

    if @sec Is Null
    raiserror( 'Usage : exec sp_KillIdleSpids <sec>, [<0 or 1>, <Message>]', 12, 1)
    return -1

    If @NetSend=1 and @msg is Null
    raiserror( 'Usage : exec sp_KillIdleSpids <sec>, [<0 or 1>, <Message>]. Please fill Message field if using ''1''', 12, 1)
    return -1

    declare U_curs scroll insensitive cursor for
    select s.spid, s.hostname
    from master..sysprocesses s
    where s.program_name LIKE '%Microsoft Dynamics NAV Classic client%'
    and (datediff( ss, s.last_batch, getdate()) > @sec)
    -- and s.loginame LIKE ''

    open U_curs

    fetch next from U_curs into @spid, @hostname

    @fetch_status = 0
    set @cmd=convert(char(4), @spid)
    exec('kill ' + @cmd)

    if @NetSend=1 -- Execute 'Net Send' only if set to 1 --
    set @cmd='net send ' + ltrim(rtrim(@hostname)) + ' ' + @msg
    exec master..xp_cmdshell @cmd, no_output

    fetch next from U_curs into @spid, @hostname


    deallocate U_curs
Sign In or Register to comment.