Kill Idle Navision Sessions (SQL scripts)

Administrator
Administrator Member, Moderator, Administrator Posts: 2,506
edited 2013-06-28 in Download section
Kill Idle Navision Sessions (SQL scripts)
This is a modification of the TSQL script from Steve Jones - www.dkranch.net I use since several years.
This new version kills only programs with "Navision" in their name and warn users with a "net send" message.
This archive contain a SQL Server job which run the script once per minute and kills idle sessions for more than 30 minutes.

http://www.mibuso.com/dlinfo.asp?FileID=367

Discuss this download here.

Comments

  • Sneaker
    Sneaker Member Posts: 6
    How to implement this on Microsoft SQL 2000 Server?
  • prabhat_sjc
    prabhat_sjc Member Posts: 5
    the scri[pts are there but how do we use these scriptp on microsoft sql server 2000 .. plzz reply soon
  • itspeter
    itspeter Member Posts: 105
    Basically, the script split into 2: -

    1.) Add job to SQL Agent. (Job sp_KillIdleSpids.sql)
    - To install it, you need to start SQL Server Agent, and change the databasename in the script into your database name. 
    @database_name = N'Navision Demo',
    Execute it in SQL Query Analyzer.
    - According to the script, it will actually schedule to run the script to perform checking every minutes between 8am to 6pm.
    - The task is stored in msdb database in tables(sysjobs, sysjobschedules, sysjobservers, and sysjobsteps)

    2.) Create store procedure. (sp_KillIdleSpids.sql)
    - This store procedure will be trigger by the job above, to kill the user from the database and net send message to their screen.

    I hope this helps.
    Regards,
    Peter Ng
  • ranian92
    ranian92 Member Posts: 10
    Hi..

    I solved this problem by above solusion

    Here, compile location is important.

    sp_KillIdleSpids.sql ==> master database
    Job sp_KillIdleSpids ==> msdb database
    8) 8) 8)


    But another problem is ....

    "Net Send ..." does not execute...


    Why not ??? ](*,) ](*,) ](*,)


    ※ Win2K Server, SQL Server 2K, Navision 4.0
  • pvar
    pvar Member Posts: 157
    Is there a way to identify all the sesssions that connected to a particular database and kill those?
  • DenSter
    DenSter Member Posts: 8,307
    There's a database name field, is that what you are looking for?
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • pvar
    pvar Member Posts: 157
    Yes, but that is not in sysprocesses table. But I got the answer from another place. You need to link sysprocesses with sysdatabases based on dbid and then you will get all the spid's connected to that database.

    Thanks for the reply though.
  • DenSter
    DenSter Member Posts: 8,307
    There's a view called 'Sessions' in your NAV database, and the links are in the definition. If you need to modify it, do it on a copy of the view.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • pvar
    pvar Member Posts: 157
    We are using SQL server database and I was looking for a script to kill the sessions before restoring a database.
  • pvar
    pvar Member Posts: 157
    Forget about my previous post. I thought you were refering to some navision table.

    The script I got does the same thing as the 'Sessions' view. Thanks anyway.
  • pakenso
    pakenso Member Posts: 1
    "NET SEND" doesn't work, but if you use terminal server better to use MSG command, so in this case you can rewrite stored procedure like following:

    set @cmd='msg ' + ltrim(rtrim(@username)) + ' /SERVER:' + ltrim(rtrim(@hostname)) + ' ' + @msg

    You also need to add new variable @username and take value from nt_username like this:

    ......
    declare u_curs scroll insensitive cursor for
    select s.spid, s.hostname, s.nt_username
    from master..sysprocesses s
    where s.program_name LIKE '%Navision%'
    and (datediff( ss, s.last_batch, getdate()) > @sec)

    open u_curs

    fetch next from u_curs into @spid, @hostname, @username
    ......
  • dareme76
    dareme76 Member Posts: 14
    Dear All,

    I have set the scripts onto an environment having SQL 2005 & Nav2009

    The issue is that when the sql scripts run, when one views file > database > information > sessions the idle users are correctly removed from the list.

    BUT

    i) The user does not receive a message indicating that he has been killed
    ii) The user can continue using navision not needing to login again.

    kindly advise

    D
  • nav_student
    nav_student Member Posts: 175
    Hi guy!

    I try to implement this example on my PC ( SQL Server 2008 + Windows 7) and try with Dynamics NAV 5.0 SP1 but so far it didn´t work.

    Below i describe the steps for implemeting this Scripts on SQL:



    1.) Add job to SQL Agent. (Job sp_KillIdleSpids.sql) - To install it, you need to start SQL Server Agent, and change the databasename in the script into your database name.
    @database_name = N'Demo Database (5-0) -> Here i pointing this step into my Database ->Demo Database (5-0), but i dont have sure because ranian92 refers to msdb database .QUESTION: HERE TO Point THIS JOB? AND I CHANGE THE TIME TO 60 seconds
    2.) Execute it in SQL Query Analyzer.
    - According to the script, it will actually schedule to run the script to perform checking every minutes between 8am to 6pm.
    - The task is stored in msdb database in tables(sysjobs, sysjobschedules, sysjobservers, and sysjobsteps)
    I EXECUTED AND THE COMAND WORKS WITHOUT ERRORS
    3.) Create store procedure. (sp_KillIdleSpids.sql)
    - This store procedure will be trigger by the job above, to kill the user from the database and net send message to their screen.
    WHERE TO CREATE THE STORE PROCEDURE? ON MY DATABASE OR ON THE MASTER DATABASE like ranian92 refers?

    LAST QUESTION: How to test. I go to Dynamics NAV > Database Information -> Sessions ->and i try to see if the inactive time for a session is greater than 1 minute. But the time is constantly moving and the number of licences do not decrease.
  • Amaraa
    Amaraa Member Posts: 153
    nav_student wrote:
    Hi guy!

    I try to implement this example on my PC ( SQL Server 2008 + Windows 7) and try with Dynamics NAV 5.0 SP1 but so far it didn´t work.

    Below i describe the steps for implemeting this Scripts on SQL:



    1.) Add job to SQL Agent. (Job sp_KillIdleSpids.sql) - To install it, you need to start SQL Server Agent, and change the databasename in the script into your database name.
    @database_name = N'Demo Database (5-0) -> Here i pointing this step into my Database ->Demo Database (5-0), but i dont have sure because ranian92 refers to msdb database .QUESTION: HERE TO Point THIS JOB? AND I CHANGE THE TIME TO 60 seconds
    2.) Execute it in SQL Query Analyzer.
    - According to the script, it will actually schedule to run the script to perform checking every minutes between 8am to 6pm.
    - The task is stored in msdb database in tables(sysjobs, sysjobschedules, sysjobservers, and sysjobsteps)
    I EXECUTED AND THE COMAND WORKS WITHOUT ERRORS
    3.) Create store procedure. (sp_KillIdleSpids.sql)
    - This store procedure will be trigger by the job above, to kill the user from the database and net send message to their screen.
    WHERE TO CREATE THE STORE PROCEDURE? ON MY DATABASE OR ON THE MASTER DATABASE like ranian92 refers?

    LAST QUESTION: How to test. I go to Dynamics NAV > Database Information -> Sessions ->and i try to see if the inactive time for a session is greater than 1 minute. But the time is constantly moving and the number of licences do not decrease.

    I have same result... Can anyone tell us how we should test? or what is wrong?
  • Amaraa
    Amaraa Member Posts: 153
    I have run it!!! \:D/

    as said the scripts should be executed as these way:
    Job sp_KillIdleSpids.sql -> msdb
    sp_KillIdleSpids.sql -> master

    and then in sp_KillIdleSpids.sql in these part you need to change the value %Navision% to something %nav% cause the program_name is running under "Microsoft Dynamics NAV client" something like that so it will select nothing.

    select s.spid, s.hostname
    from master..sysprocesses s
    where s.program_name LIKE '%Navision%'

    After this you need to enable xp_cmdshell. In order to do that you need to go to SQL Surface Area Configuration and enable it.

    To enable the netsend you need to start the service.

    thats all folks
  • Ravi_Thakkar
    Ravi_Thakkar Member Posts: 392
    Hello All,

    Specified SQL Scripts are running successfully in my SQL Server without any error.
    I have modified Program Name filter to '%Dynamics%'.

    But, My NAV Classic and RTC are not going to close on end user PC.
    What can be the issue?

    Please, help me.
    Thanks.
    Ravi_Thakkar
    Ahmedabad, Gujarat, India
    E Mail : ravi.thakkar@hotmail.com
  • sunil_knd
    sunil_knd Member Posts: 1
    I have sql 2008 and the job retourn me this error:

    Don't exist a cursor named 'U_curs'. [SQLSTATE 34000] (Error 16916). The step failed.

    Someone can help me?
  • eYe
    eYe Member Posts: 169
    Hi

    I wrote something in NAV Codeunit 1 that kills the sessions. Except that it only works on a native database.
    In SQL it deletes the entry from the Session table, and you can see it is gone, but as soon as the user opens a form the session is back again.

    So I downloaded this, implemented it and the same result!
    Session entry is removed, but when you click on a menu item or navigate through records a new session entry is created with a new session ID. :?:

    Regards,
    Kind Regards,
    Ewald Venter
  • tolkata
    tolkata Member Posts: 2
    Hello,

    I have sql 2005 and I have the same problem like sunil_knd, the job return me this error:

    A cursor with the name 'U_curs' doesn't exist. [SQLSTATE 34000] (Error 16916). The step failed.

    What could be the cause for such a problem?

    Regards.
  • tolkata
    tolkata Member Posts: 2
    Hi ...

    I fixed the problem. Actually it was something simple but ... Anyway, the problem was that in fact a cursor with the name 'U_curs' doesn't exist!!! It exists but not with capital letter. I changed the capital 'U_curs' to 'u_curs' in the file sp_KillIdleSpids.sql at line ... deallocate U_curs and this error message doesn't appear any more.

    Regards.
  • sebius
    sebius Member Posts: 5
    dareme76 wrote:
    Dear All,

    I have set the scripts onto an environment having SQL 2005 & Nav2009

    The issue is that when the sql scripts run, when one views file > database > information > sessions the idle users are correctly removed from the list.

    BUT

    i) The user does not receive a message indicating that he has been killed
    ii) The user can continue using navision not needing to login again.

    kindly advise

    D

    Dear All,
    I Have same problem too. But SQL 2008 & Nav2009 R2
    i) The user does not receive a message indicating that he has been killed
    ii) The user can continue using navision not needing to login again.
    iii) New user can login If Old Users continue using navision then overflow the licence

    How to fix this issue pls kindly advise
  • vicky_dada
    vicky_dada Member Posts: 106
    was this issue solved? even the same is happening in my case

    "I Have same problem too. But SQL 2008 & Nav2009 R2
    i) The user does not receive a message indicating that he has been killed
    ii) The user can continue using navision not needing to login again.
    iii) New user can login If Old Users continue using navision then overflow the licence"
    sebius wrote:
    dareme76 wrote:
    Dear All,

    I have set the scripts onto an environment having SQL 2005 & Nav2009

    The issue is that when the sql scripts run, when one views file > database > information > sessions the idle users are correctly removed from the list.

    BUT

    i) The user does not receive a message indicating that he has been killed
    ii) The user can continue using navision not needing to login again.

    kindly advise

    D

    Dear All,
    I Have same problem too. But SQL 2008 & Nav2009 R2
    i) The user does not receive a message indicating that he has been killed
    ii) The user can continue using navision not needing to login again.
    iii) New user can login If Old Users continue using navision then overflow the licence

    How to fix this issue pls kindly advise
  • Tonysdq
    Tonysdq Member Posts: 2
    I implemented this solution in my environment and executed and it worked perfectly from my view. Since it worked great, I created a job in SQL to run by COB. It run for 2 days until I started to hear from users that they were loosing their settings.

    I have disabled this until I figure out what exactly is going on.

    Has anyone experience this as well?
  • chets0810
    chets0810 Member Posts: 5
    Script for kill SQL session in Navision DB..


    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_KillIdleSpids]') and
    OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[sp_KillIdleSpids]
    go
    create procedure [dbo].[sp_KillIdleSpids]
    as
    declare
    --@sec int=null,
    --@diff int,
    @spid int,
    @cmd char(255),
    @loopcount int

    set @loopcount=( select count(s.spid) as count1 from master..sysprocesses s
    where program_name LIKE '%NAV%'
    and ((select DATEDIFF(SS,s.last_batch,GETDATE()))>180) ) --180 stands for 3 minutes

    while @loopcount!=0
    begin
    set @spid=(select top 1 s.spid
    from master..sysprocesses s where program_name LIKE '%NAV%'
    and ((select DATEDIFF(SS,s.last_batch,GETDATE()))>1))
    set @cmd=convert(char(4), @spid)
    exec( 'kill ' +@cmd);
    SET @loopcount =@loopcount-1
    end


    Create the SQL JOB

    define in step
    exec sp_KillIdleSpids

Categories