Kill Idle Sessions - NAV RTC

Ravi_Thakkar

Hello All,

Is there any logic or Session Killer available to kill the idle sessions for NAV RTC?
I have seen all session killers, available for NAV Classic versions.

Please, help me if anybody knows.

Thanks.

manisharma31

hi,
If you see in File ->Database -> Information.
You will see the application name "Microsoft Dynamics NAV RTC" is avaliable.
As you have tired many session killers,try to add the the application name in then for RTC.

Ravi_Thakkar

Thanks a lot for your reply.

I wrote 'I have seen' means, I know session killer forums in mibuso.
But, no idea regarding.
Can you suggest me how to kill sessions of RTC???

alexpeck

Idle sessions are automatically closed by the server (after 15 minutes I think), so you shouldn't really need to kill connections manually.

In SQL Server you can query the master.dbo.sysprocesses view and look for RTC clients (program_name column) connected to the NAV database (dbid column - execute db_id('name of your nav database') to find the id). If you want to kill an RTC connection you can use the kill keyword in SQL Server in conjunction with the spid you found in sysprocesses.

This will kill the session, so if you accidentally do this to an active user they might lose some data. RTC is able to automatically reconnect if the connection has been dropped, but it can't always preserve all the state in the user's pages.

Alex

Ravi_Thakkar

Thanks u all for replies.

My query has been solved by SQL Scripts found on Mibuso forums only.

Thanks.

wic

Hi,
I've a customer installation which keeps sessions open longer that 2 days... (idle time) and once a week, I've to manually kill the sessions in order to permit the customer to login!?!?

NAV2009SP1 build 31320

whassup?
:-k

regards
Chris

Sabino

I make a little free utility to do it at www.samplon.com

wic

Gracias Sabino. Unfortunately, "The truth is out there", I mean, people are disconnected from NAV, the computer is shut down but the sessions remains on the SQL server! =;
So the tool should analyse open sessions on a SQL Server... :-k

Finally, I don't want a tool, [-( I want a native solution (maybe R2 or build 456789) because I think the problem comes from the "NAV Server" which doesn't disconnect correctly the users.

Sandeep_Poonia

go sql machine and run follwing
>>>>>>Job sp_KillIdleSpids.sql>>>>>>
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Kills inactive Navision sessions')
IF (@JobID IS NOT NULL)
BEGIN

IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN

RAISERROR (N'Cannot import « Kills inactive Navision sessions » because multiserver job already existing.', 16, 1)
GOTO QuitWithRollback
END
ELSE
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Kills inactive Navision sessions'
SELECT @JobID = NULL
END

BEGIN

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Kills inactive Navision sessions', @owner_login_name = N'sa', @description = N'Kills the inactive sessions for more than 30 minutes with a warning message.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Kill idle spids 1800 sec.', @command = N'sp_KillIdleSpids 1800, 1, ''Stopped session (inactive for more than 30 minutes). Please close and start again your Navision client''', @database_name = N'Navision', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'1 time per minute between 08h00 and 18h00', @enabled = 1, @freq_type = 4, @active_start_date = 20021007, @active_start_time = 80000, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 180000
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


and then run one more
>>>>>>>>>sp_KillIdleSpids.sql>>>>>>>>>>>>>>>
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

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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

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

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

Author : Steve Jones - http://www.dkranch.net

Modifications : Benoît Ourssaire - http://www.soft-business.fr


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
begin
raiserror( 'Usage : exec sp_KillIdleSpids <sec>, [<0 or 1>, <Message>]', 12, 1)
return -1
end

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



declare u_curs scroll insensitive cursor for
select s.spid, s.hostname
from master..sysprocesses s
where s.program_name LIKE '%Dynamics%'
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
begin
set @cmd=convert(char(4), @spid)
exec('kill ' + @cmd)

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

fetch next from u_curs into @spid, @hostname
end

deallocate U_curs
return
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

wic

thank you..I think this could be a corect answer BUT: it still no NATIVE solution... ](*,) I think I've to test with the newest R2 client or next upcoming builds.

For me , this can only be a build (exe/dll) problem. :-k

Regards
Chris

sebius

Dear All,

It work!! I use SQL 2008 & Nav2009 R2 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

How to fix this issue pls kindly advise

Ravi_Thakkar

That is not an issue actually.

On user Defined Idle time, system makes the User License free (Since up to the User Defined Idle time has reached).

So, as soon as the Cursor make the movement on the Idle screen, it directly assign the free user license to that session...if available.