Dear all,
How to delete Nav sessions through SQL query ?
Hi!
Well, this would be the KILL command:
KILL { *session ID* | *UOW* } [ WITH STATUSONLY ]
See “Books Online” about details.
… and why do you want to do this?
Thanks Jorg.
Can I delete the all Records from that table ?
Well, technically you could kill any process on the SQL Server, but I guess you are referring to the “Session” list visible in NAV (which is actually a “View”). Here a little script which - for example - kills all NAV sessions which are idle for more than 6 hours:
use [master]
go
set statistics io off
set nocount on
go
declare @threshold datetime
set @threshold = dateadd(hour, -6, getdate()) – threshold is 6 hours ago
print 'Killing processes which executed “Last Batch” before ’ + convert(varchar(30), @threshold, 113)
print ‘’
declare @spid int, @last_batch datetime
declare spid_cur cursor for
select [spid], [last_batch] from sysprocesses
where ([program_name] = ‘Microsoft Business Solutions-Navision client’) OR ([program_name] = ‘Microsoft Dynamics NAV client’) – change according to NAV version
and [last_batch] <= @threshold
open spid_cur
fetch next from spid_cur into @spid, @last_batch
while @@fetch_status = 0 begin
print 'Killing process ’ + convert(varchar(5), @spid) + ', idle since ’ + convert(varchar(30), @last_batch, 113)
exec ('kill ’ + @spid)
fetch next from spid_cur into @spid, @last_batch
end
close spid_cur
deallocate spid_cur
go
(PROVIDED AS IS, USE AT OWN RISK) You could modify this to suit your purposes.
But still the question is: why you want to do this? “Killing” NAV sessions might cause data corruption, depending on the business process you kill …
Hi there,
I would like to step in here and ask a question regarding the above script.
We are indeed looking for a solution like that, played a bit with a Navision session killer, but somehow that didn’t work.
Well from time to time we HAVE to kick some idle - sessions (of course due to a limitation of licenses).
So, coming to my question:
Is there a difference between killing the session via Navision (database → sessions) or via this exec ('kill ’ + @spid)
command? I would really like to have something like ‘kill the session of a user where last_batch is longer then x min ago’.
I understand that the data on the screen of the person might be lost, but can there be any other impact to the database itself?
Thanks in advance!
Jorg’s script seems perfect for what you are seeking. His script will only kill a SPID (database thread) where the last SQL statement issued by that SPID was more than six (6) hours ago, and for specific program names. One suggested enhancement would be to include an additional predicate/filter such that a system thread is never accidentally KILL’ed (e.g. and loginame <> ‘sa’). It shouldn’t happen since the script is checking for particular program names, but I am always overly cautious when automating KILL commands.
I think it is a low probability that a program is actively doing something with data and has not issued a SQL statement on the same SPID for 6+ hours. However, nothing is risk-free. You might enhance the script to provide further focus for the eligible SPIDs to KILL. Kill or never kill for particular hosts? Kill or never kill for particular logins?
Hi there,
thanks a lot for your reply. Sounds great, I guess setting the correct parameters / filters in order to avoid accidentally killing a wrong process / user shouldn’t be a big deal.
Not sure though about every bit of the above code, but I will hopefully be able to find that out by myself.
Kind regards