Zombie finsql processes

bhalpin

Hi.

Environment: NAV 5.0 SP0, Win 2003 server, SQL 2005.

To run a custom NAV import/export, we remote-desktop to the server, run the NAV SQL client, and open the SQL database on that system's SQL server. During testing and now in semi-production, we run into cases where when we exit NAV the log file the export produces is locked open. Looking at task manager we see a finsql.exe process tat shouud not be there. When we kill that process, the log file is released.

This happens fairly regularly - it can be reproduced easly by booting the NAV client, doing an import, and then closing the client. After two or three iterations a zombie process remains.

The import/export is fairly 'clean' - it is reading and writing plain-text files, and appears to be operating correctly - until it runs into the case where the log it writes to is still locked open by the zombie.

Has anyone seen anything like this and maybe suggest what to look for?

Thanks in advance.

kine

Try it with newer client (NAV 5.00SP1+Updates). If the error will not be there, it is a bug.

bhalpin

Thanks - will try it.

idiot

No harm checking for a File.CLOSE statement at the end...

stryk

Hi!
As some kind of workaround you could also run a job, periodically "killing" idle processes:

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'  -- change if applicable
    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

USE AT OWN RISK!

bhalpin

stryk: Interesting ... but dumb question: What language is that?

idiot: Yes, will check. I had left one out previously, and that caused some havoc. Maybe I've missed another.

stryk

Oh ... that's "Transact SQL" (TSQL), the Microsoft variant of SQL. This code could be copied into a "Job Step" (type "TSQL") of a SQL Agent Job, and maybe scheduled to run every 6 hours.

This code should look up all sessions from a 'Microsoft Business Solutions-Navision client' application which have execute the "last batch" (the last command/statement/query) 6 hours ago to "kill" those processes.

If you're not familiar with TSQL (or SQL) then maybe you should restrain from using it, as the potential risk of this it, that a session is killed which should not - this kind of "exception handling" needs to be added (if required).

bhalpin

Interesting.

But don't worry - I know just enough SQL to leave stuff like this to the gurus.

Thanks though