Kill Idle Navision Sessions (SQL scripts)

Administrator

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.

Sneaker

How to implement this on Microsoft SQL 2000 Server?

prabhat_sjc

the scri[pts are there but how do we use these scriptp on microsoft sql server 2000 .. plzz reply soon

itspeter

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.

ranian92

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

Is there a way to identify all the sesssions that connected to a particular database and kill those?

DenSter

There's a database name field, is that what you are looking for?

pvar

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

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.

pvar

We are using SQL server database and I was looking for a script to kill the sessions before restoring a database.

pvar

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

"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

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

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

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

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

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.

sunil_knd

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

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,

tolkata

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

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

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

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

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

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