Kill Idle Navision Sessions (SQL scripts)

AdministratorAdministrator Posts: 2,233Member, Moderator, Administrator
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

  • SneakerSneaker Posts: 6Member
    How to implement this on Microsoft SQL 2000 Server?
  • prabhat_sjcprabhat_sjc Posts: 5Member
    the scri[pts are there but how do we use these scriptp on microsoft sql server 2000 .. plzz reply soon
  • itspeteritspeter Posts: 105Member
    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
  • ranian92ranian92 Posts: 10Member
    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
  • pvarpvar Posts: 157Member
    Is there a way to identify all the sesssions that connected to a particular database and kill those?
  • DenSterDenSter Posts: 8,045Member
    There's a database name field, is that what you are looking for?
  • pvarpvar Posts: 157Member
    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.
  • DenSterDenSter Posts: 8,045Member
    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.
  • pvarpvar Posts: 157Member
    We are using SQL server database and I was looking for a script to kill the sessions before restoring a database.
  • pvarpvar Posts: 157Member
    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.
  • pakensopakenso Posts: 1Member
    "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
    ......
  • dareme76dareme76 Posts: 14Member
    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_studentnav_student Posts: 224Member
    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.
  • AmaraaAmaraa Posts: 153Member
    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?
  • AmaraaAmaraa Posts: 153Member
    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_ThakkarRavi_Thakkar Posts: 471Member
    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_kndsunil_knd Posts: 1Member
    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?
  • eYeeYe Posts: 166Member
    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,
  • tolkatatolkata Posts: 2Member
    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.
  • tolkatatolkata Posts: 2Member
    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.
  • sebiussebius Posts: 5Member
    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
    Capture.JPG
    22K
    Capture.JPG 22.2K
  • vicky_dadavicky_dada Posts: 106Member
    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
  • TonysdqTonysdq Posts: 2Member
    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?
  • chets0810chets0810 Posts: 3Member
    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
Sign In or Register to comment.