mibuso.com

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.

How to implement this on Microsoft SQL 2000 Server?

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

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.

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.

Hi..

I solved this problem by above solusion

Here, compile location is important.

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



But another problem is ....

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


Why not ???


※ Win2K Server, SQL Server 2K, Navision 4.0

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

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

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.

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.

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

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.

"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
......

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

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?