5.0 session table

ara3n

Anybody has run into this problem? on sql 2k5

If you click on file->database->information->session. and scroll through the records. navision locks up and you have to kill the client.

sgg

My SQL 2k5 Error Log for Application reads as follows after the Hanging Dynamics NAV SQL Session :

03/20/2007 10:48:16,Application Hang,Error,Hanging application finsql.exe<c/> version 5.0.24029.0<c/> hang module hungapp<c/> version 0.0.0.0<c/> hang address 0x00000000.,(101),1002,,SGG1

What could be the source of this Hanging Problem?

kriki

[Topic moved from Upcoming version NAV 5.1 forum to Navision forum]

ultiboy

We encountered the same thing. Has anybody find the reason why (and possibly a solution)?

Seb

DenSter

I got a hanging app just form resizing the sessions window

Poltergeist

I have the same problem. Also the processor goes up to 100% (or 50% with a dual core, 25% with a quad core processor) on the workstation.

Just posted an supportrequest with Microsoft... Let's see what's happening now...

Poltergeist

And got an answer: the error is known, and will be solved in the next update, scheduled for the end of august

ara3n

Great.

Scott_Frappier

A challenge!

4.0 SP3 SQL Syntax:

SELECT  * FROM "Navision Demo Database (4_0 SP3)"."dbo"."Session" WHERE  "Connection ID">=@P1 ORDER BY "Connection ID" OPTION (FAST 10)

5.0 SQL Syntax:

SELECT  * FROM "Dynamics NAV (5_0) Demo Database"."dbo"."Session" WHERE  "Connection ID"<@P1 ORDER BY "Connection ID" DESC

When watching the SQL Server Profiler...it hangs on the following statement:

FETCH API_CURSOR0000000000000016

Once this statement is called, Dynamics NAV hangs at around 40% utilization on my development box. After a period of time, it recovers...

What's interesting about this issue is the fact that the sort order is not by Connection ID as I'd expect. In Dynamics NAV 4.0 SP3, this issue does not occur, but in 5.0, it does...

So...what does this all mean?!

It looks to me that NAV is not getting distinct records upon the query, and for some reason, when the subquery is executed, it causes an issue in which the client locks up because it's trying to fetch a cursor position that does not exist.

How do we fix it?

Easy...set the database session table so that it is a "SELECT DISTINCT"...while there is a performance/compatibility issue with this (potentially), it seems to do the trick:

USE [Dynamics NAV (5_0) Demo Database]
GO
/****** Object:  View [dbo].[Session]    Script Date: 07/13/2007 23:49:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Session]
AS
SELECT DISTINCT 
                      CAST(SP.spid AS INTEGER) AS [Connection ID], CAST(RTRIM(SP.loginame) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [User ID], 
                      CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session], CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), SP.login_time, 
                      108), 120) AS [Login Time], CONVERT(DATETIME, CONVERT(CHAR(10), SP.login_time, 120) + ' 00:00:00:000', 121) AS [Login Date], 
                      CAST(SD.name AS NVARCHAR(128)) COLLATE Latin1_General_CS_AS AS [Database Name], CAST(RTRIM(SP.program_name) 
                      AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Application Name], CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS [Login Type], 
                      CAST(RTRIM(SP.hostname) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Host Name], SP.cpu AS [CPU Time (ms)], 
                      CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage] * 8 END AS [Memory Usage (KB)], SP.physical_io AS [Physical I_O], 
                      CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS Blocked, CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) 
                      AS [Wait Time (ms)], CAST(SP.blocked AS INTEGER) AS [Blocking Connection ID], CAST(ISNULL(RTRIM(SPB.loginame), '') 
                      AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking User ID], CAST(ISNULL(RTRIM(SPB.hostname), '') 
                      AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking Host Name], CAST('' AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking Object], 
                      CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 ELSE 0 END AS [Idle Time]
FROM         master.dbo.sysprocesses AS SP INNER JOIN
                      master.dbo.sysdatabases AS SD ON SP.dbid = SD.dbid LEFT OUTER JOIN
                      master.dbo.sysprocesses AS SPB ON SP.blocked = SPB.spid
WHERE     (SP.ecid = 0) AND (SP.last_batch >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120))

Make sure to change the database to the correct database. YMMV with this modification, but it did the trick for me...no more lock-ups and I could change the sort orders without issues.

Enjoy!

- Scott

ara3n

Yep it sure did. It doesn't hang anymore.

Thanks a lot.

Poltergeist

Scott, this does work wonderfully. However, could this have any influence on the next update? I don't mind running this on testservers, but on client databases, I am a bit more reluctant...

DenSter

So.... simply changing the dbo.Session view from "SELECT" to "SELECT DISTINCT" solves the hanging app?

ara3n

yep

Poltergeist

As per the latest (and probably last) case update, the update should be released end of july/early august. I've also pointed Microsoft to this post, and the technical support engineer forwarded the workaround to the product team, so that they can maybe test their solution with this workaround...

JParinya

Thank you so much

Tomas

I noticed that this "lock-up" happens only for databases which security model is set to Enhanced. While dealing with security permissions, changing it to Standard most of the times solved this issue as well.

Bjarne_Astrup

The problem is fixed in build 24917 (KB938400)

https://premier.microsoft.com/default.a ... -us;938400

Login is required.

/Bjarne

ara3n

Tomas wrote:

I noticed that this "lock-up" happens only for databases which security model is set to Enhanced. While dealing with security permissions, changing it to Standard most of the times solved this issue as well.

I was at a client right now that had standard security model and it would still lock up.