Hi, all
I’ve got an interesting problem, current we are using NAV5.0, and Sql2005, the problem is these days I found out there are more than 1000 session established by NAV to the DB, however We only have around 45 users. When I use sp_who2 I found out the problem and the Command is awaiting command and status is sleeping. but when I open NAV and found out the current session just 35.
ANY COMMENTS about the problem?
Thanks
Can you tell what you get when you run View called session in your NAV database?
| SPID | Status | Login | BlkBy | Command | CPUTime | DiskIO | ProgramName | SPID1 |
|---|---|---|---|---|---|---|---|---|
| 1000 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1000 |
| 1001 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1001 |
| 1002 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1002 |
| 1003 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1003 |
| 1004 | sleeping | XX\SQLService | . | AWAITING COMMAND | 15 | 0 | Microsoft Dynamics NAV Application Server | 1004 |
| 1005 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1005 |
| 1006 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1006 |
| 1007 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1007 |
| 1008 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1008 |
| 1009 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1009 |
| 1010 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1010 |
| 1011 | sleeping | XX\SQLService | . | AWAITING COMMAND | 15 | 0 | Microsoft Dynamics NAV Application Server | 1011 |
| 1012 | sleeping | XX\SQLService | . | AWAITING COMMAND | 16 | 0 | Microsoft Dynamics NAV Application Server | 1012 |
I get these info from sp_who2, the info shown you above just part of it
How did you determine that those sessions are established by NAV? It looks to me like there is a user called ‘SQLService’, which would indicate a service that is trying to connect. Is that a NAS instance perhaps, that is not able to log in? Did you check the Windows Event Log?
Hi, the table is too long so some col just missing, actually when run sp_who2 there is a column name ProgramName this column will show you which app connect to the DB. The windows log looks OK. this problem just happened last week.
the strange thing is NAV5 on the SQL server is in a different instance. which is xxx-xxx-1\nav. on that instance when I execuate sp_who2 it looks well, but on xxx-xxx-1 there are too mang login.
really confusing!!!
Hi!
Uhh … so you have NAV Logins on instance xxx-xxx-1 even though there is not NAV database installed? To which database are these processes connected then, “master” I suppose? Can’t you simply deny the login to this instance? And all these sessions are inactive, means AWAITING COMMAND … when was the last batch executed?
Well, finding out where those “zombie-sessions” are coming from is one thing, but here a “quickie” how you could get rid of them:
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’ – name depends on 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
This TSQL script would look for “NAV Client” sessions which have executed the “Last Batch” 6 hours ago; those sessions would be killed. Please change it as you demand - and TEST IT CAREFULLY (e.g. by commenting out the “exec ('kill ’ + @spid)” thing!
This script e.g. could be executed via SQL Agent Job, thus periodically cleaning up the system. USE AT OWN RISK!
Is there a customization that uses ADO commands to connect to the database, but that does not explicitly close the connection at the end of the function? Sometimes those automation type things are picky about housekeeping.
this may help you. It is a stored proc that I developed to kill idle sessions:
USE
[YOUR DATABASE NAME HERE]
GO
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
procedure [dbo].[sp_KillIdleSpids]
@sec int=3600 as --1 hours
DECLARE
@ERR INT,
@SPID
INT,
@CMD
CHAR(255),
@HOSTNAME
CHAR(255),
@LOGINAME
CHAR(255)
DECLARE
u_curs SCROLL INSENSITIVE CURSOR
FOR
SELECT s.spid, s.hostname, s.loginame
from master…sysprocesses s
WHERE s.program_name LIKE ‘%YOUR_DATABASE_NAME_HERE%’
and (datediff( ss, s.last_batch, getdate()) > @sec)
AND (loginame <> ‘KISLOG-PT\nav.adcs’)
OPEN
u_curs
FETCH
NEXT FROM u_curs INTO @spid, @hostname, @loginame
WHILE @@fetch_status = 0
BEGIN
SET @cmd=convert(char(4), @spid)
EXEC('kill ’ + @cmd)
FETCH NEXT FROM u_curs into @spid, @hostname, @loginame
END
CLose
u_curs
DEALLOCATE
U_curs
RETURN