“ALL ABOUT” Kill (In)active Sessions when “no more licenses”
Sometimes it happens that there are “no more licenses available” for Dynamics NAV, from NAV 2013 R2 and next releases, it’s necessary to run the Windows Client or to use Powershell statements to kill some sessions and unlock others.
If you are unable to access to NAV (“no more licenses”), you should need to use SQL Console and kill sessions from there (reading from Active Sessions Table of from sysprocesses View on master db..)
If you are unable to access to NAV (“no more licenses”), you should need to use SQL Console and kill sessions from there (reading from Active Sessions Table of from sysprocesses View on master db..)
“Some user don’t want to use SQL Server to achieve this !” …
WARNING : “No more licenses !!” … Immediately Buy Others !!!
There are many ways to kill sessions, i list below some methods.
With NAV Server Login
– C/AL
– Powershell
– C/AL
– Powershell
Without NAV Server Login
– SQL Server Stored Procedures\T-Sql
– VB .NET Application
– SQL Server Stored Procedures\T-Sql
– VB .NET Application
Below some methods to kill the sessions even in the case of “no more licenses available“: SQL Script and VB.NET
C/AL Statement to Kill Sessions
The Standard Statement for C/AL Code is: STOPSESSION(“Session ID”);
Example:
IF CONFIRM (‘Kill Session ?’) THEN STOPSESSION(“Session ID”) … GOAL !
The Standard Statement for C/AL Code is: STOPSESSION(“Session ID”);
Example:
IF CONFIRM (‘Kill Session ?’) THEN STOPSESSION(“Session ID”) … GOAL !
…. BUT YOU NEED TO CONNECT TO NAV SERVER ! .. sometimes is NOT possible!
———————————————————-
My VB.NET Solution: “SESSION KILLER v.3.0”
“I wrote a simple solution on VB.NET to Kill Active Sessions using a SQL Connections,
you cand find it on GITHUB and TECHNET.”
“I wrote a simple solution on VB.NET to Kill Active Sessions using a SQL Connections,
you cand find it on GITHUB and TECHNET.”
ON GITHUB
VB.NET-SQL-Kill-Sessions
https://github.com/rstefanetti/NAV_SQL/tree/BV-.NET-SQL-Kill-Sessions
https://github.com/rstefanetti/NAV_SQL/tree/BV-.NET-SQL-Kill-Sessions
ZIP COMPLETE SOLUTION
https://github.com/rstefanetti/NAV_SQL/compare/BV-.NET-SQL-Kill-Sessions
https://github.com/rstefanetti/NAV_SQL/compare/BV-.NET-SQL-Kill-Sessions
Session KIller V3.0
ON TECHNET
Post link
Download link
About My VB.NET Simple Solution, some pictures
App Config File
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name=”NAV_2017_KIll_SQL_Inactive_Sessions.My.MySettings.Demo_Database_NAV__10_0_ConnectionString”
connectionString=”Data Source=STEFANETTILEN\SQL2012ENT;Initial Catalog="Demo Database NAV (10-0)";Integrated Security=True”
providerName=”System.Data.SqlClient” />
</connectionStrings>
<startup>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.5″ />
</startup>
</configuration>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name=”NAV_2017_KIll_SQL_Inactive_Sessions.My.MySettings.Demo_Database_NAV__10_0_ConnectionString”
connectionString=”Data Source=STEFANETTILEN\SQL2012ENT;Initial Catalog="Demo Database NAV (10-0)";Integrated Security=True”
providerName=”System.Data.SqlClient” />
</connectionStrings>
<startup>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.5″ />
</startup>
</configuration>
———————————————————-
KILL SESSIONS FROM SQL STATEMENTS
KILL SESSIONS FROM SQL STATEMENTS
KILL (Transact-SQL)
– Syntax for SQL Server, Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
– Syntax for SQL Server, Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
KILL ‘session_id’ [;]
Source https://msdn.microsoft.com/it-it/library/ms173730.aspx
KEEP IN MIND !!! DON’T KILL PROCESSES IN THIS STATES:
-AWAITING COMMAND
-CHECKPOINT SLEEP
-LAZY WRITER
-LOCK MONITOR
-SIGNAL HANDLER
-AWAITING COMMAND
-CHECKPOINT SLEEP
-LAZY WRITER
-LOCK MONITOR
-SIGNAL HANDLER
SOURCE SQL SCRIPTS
Source https://www.codeproject.com/Tips/647839/Session-Killer-for-SQL-Server
Source https://technet.microsoft.com/en-us/library/ms173730.aspx
Source https://www.codeproject.com/Tips/647839/Session-Killer-for-SQL-Server
Source https://technet.microsoft.com/en-us/library/ms173730.aspx
My Customized release
1) SP_SQL_KillNavServerSession
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: KILL ALL NAV ACTIVE USER SESSSIONS WITH MULTIPLE INSTANCES
— =============================================
CREATE PROCEDURE [dbo].[SP_SQL_KillNavServerSession]
AS
BEGIN
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
— Description: KILL ALL NAV ACTIVE USER SESSSIONS WITH MULTIPLE INSTANCES
— =============================================
CREATE PROCEDURE [dbo].[SP_SQL_KillNavServerSession]
AS
BEGIN
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
SET @RowIndex = 1;
SELECT @MaxIndex =
(SELECT top 1 MIN([Session ID])
FROM [ITA].[dbo].[Active Session]
WHERE [User ID] = ‘************’
group by [Session ID])
SELECT @MaxIndex =
(SELECT top 1 MIN([Session ID])
FROM [ITA].[dbo].[Active Session]
WHERE [User ID] = ‘************’
group by [Session ID])
— Remove Active Session
DELETE FROM [DB].[dbo].[Active Session] where [Session ID]>@MaxIndex AND [User ID] = ‘*********’
END
DELETE FROM [DB].[dbo].[Active Session] where [Session ID]>@MaxIndex AND [User ID] = ‘*********’
END
2) SP_SQL_KillNONavSession
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: KILL SLEEPING NO NAV SESSIONS
— =============================================
CREATE PROCEDURE [dbo].[SP_SQL_KillNoNavSession]
AS
BEGIN
DECLARE @Session TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[sid] int,
ltime DATETIME,
hname varchar(MAX),
uid varchar(100),
lret datetime,
etime int
);
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: KILL SLEEPING NO NAV SESSIONS
— =============================================
CREATE PROCEDURE [dbo].[SP_SQL_KillNoNavSession]
AS
BEGIN
DECLARE @Session TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[sid] int,
ltime DATETIME,
hname varchar(MAX),
uid varchar(100),
lret datetime,
etime int
);
DECLARE @NotAffectedUserList TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[uid] varchar(MAX)
);
(
id INT IDENTITY(1,1) PRIMARY KEY,
[uid] varchar(MAX)
);
DECLARE @session_time as int;
DECLARE @vSID as int;
DECLARE @command as varchar(MAX);
DECLARE @vSID as int;
DECLARE @command as varchar(MAX);
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
DECLARE @MaxIndex AS INT;
set @session_time=15; — Assign Session time in Minutes
— Assign List of Users who “will not be affected” by this Session Kill
INSERT into @NotAffectedUserList SELECT ‘sa’
INSERT into @NotAffectedUserList SELECT ‘NT AUTHORITY\SERVIZIO DI RETE’
INSERT into @NotAffectedUserList SELECT ‘**************’
INSERT into @NotAffectedUserList SELECT ‘scheduler’
INSERT into @NotAffectedUserList SELECT ‘sa’
INSERT into @NotAffectedUserList SELECT ‘NT AUTHORITY\SERVIZIO DI RETE’
INSERT into @NotAffectedUserList SELECT ‘**************’
INSERT into @NotAffectedUserList SELECT ‘scheduler’
INSERT into @Session
SELECT
session_id as [Session ID],
login_time as [Login Time],
host_name as [Host Name],
LOGIN_NAME as [Login ID],
last_request_end_time as [Last Request End Time],
DATEDIFF(minute, last_request_end_time,getdate()) as[Elapsed Time (minutes)]
FROM
sys.dm_exec_sessions
WHERE
open_transaction_count = 0
AND host_name <> ‘NAVSRV’ COLLATE Latin1_General_CI_AS
AND host_name is not NULL
AND DATEDIFF(minute, last_request_end_time,getdate())>=@session_time
–AND client_version=4
AND login_name COLLATE Latin1_General_CI_AS not in
(
SELECT DISTINCT uid
FROM @NotAffectedUserList
)
SELECT
session_id as [Session ID],
login_time as [Login Time],
host_name as [Host Name],
LOGIN_NAME as [Login ID],
last_request_end_time as [Last Request End Time],
DATEDIFF(minute, last_request_end_time,getdate()) as[Elapsed Time (minutes)]
FROM
sys.dm_exec_sessions
WHERE
open_transaction_count = 0
AND host_name <> ‘NAVSRV’ COLLATE Latin1_General_CI_AS
AND host_name is not NULL
AND DATEDIFF(minute, last_request_end_time,getdate())>=@session_time
–AND client_version=4
AND login_name COLLATE Latin1_General_CI_AS not in
(
SELECT DISTINCT uid
FROM @NotAffectedUserList
)
SET @RowIndex = 1;
SELECT @MaxIndex = MAX(id) FROM @Session;
SELECT @MaxIndex = MAX(id) FROM @Session;
WHILE( @RowIndex <= @MaxIndex)
BEGIN
SELECT @vSID = sid
FROM @Session
WHERE id = @RowIndex;
BEGIN
SELECT @vSID = sid
FROM @Session
WHERE id = @RowIndex;
— String together the KILL statement
SELECT @command = ‘KILL ‘ + CAST(@vSID AS varchar(5))
SELECT @command = ‘KILL ‘ + CAST(@vSID AS varchar(5))
BEGIN TRY
— Execute the final string to KILL the spids
EXEC (@command)
END TRY
BEGIN CATCH
declare @emsg varchar(500);
SELECT
@emsg=ERROR_MESSAGE();
EXEC (@command)
END TRY
BEGIN CATCH
declare @emsg varchar(500);
SELECT
@emsg=ERROR_MESSAGE();
print @emsg;
END CATCH
END CATCH
SET @RowIndex = @RowIndex + 1;
END;
END
END;
END
// KILL SQL INACTIVE SESSIONS FOR NAV APPLICATIONS
// ONLY FOR SLEEPING SESSIONS – OLD NAV CLIENTS
// ONLY FOR SLEEPING SESSIONS – OLD NAV CLIENTS
a very Simple SQL Stored Procedure to ‘kill’ sql inactive sessions
idle (sleeping) for 10 minutes related to “Nav Client” applications
idle (sleeping) for 10 minutes related to “Nav Client” applications
*** it’s necessary to identify correct “application name” ***
*** ex: program_name LIKE N’%NAV client% *** or %NAV Web%
*** ex: program_name LIKE N’%NAV client% *** or %NAV Web%
DECLARE @v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND loginame <> ‘DIR%’
AND program_name LIKE N’%NAV client%’
AND status=’sleeping’
AND DATEDIFF(mi,last_batch,GETDATE())>=10
AND spid <> @@spid
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND loginame <> ‘DIR%’
AND program_name LIKE N’%NAV client%’
AND status=’sleeping’
AND DATEDIFF(mi,last_batch,GETDATE())>=10
AND spid <> @@spid
OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT ‘Killing ‘+ CONVERT(VARCHAR,@v_spid)+’…’
EXEC(‘KILL ‘+ @v_spid)
FETCH NEXT FROM c_Users INTO @v_spid
END
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT ‘Killing ‘+ CONVERT(VARCHAR,@v_spid)+’…’
EXEC(‘KILL ‘+ @v_spid)
FETCH NEXT FROM c_Users INTO @v_spid
END
CLOSE c_Users
DEALLOCATE c_Users
DEALLOCATE c_Users
———————————————————-
OTHERS NICE SOLUTIONS (NAV LOGIN IS REQUIRED)
Olof Simren Solution
Kill Sessions in Dynamics NAV (from NAV Page)
Source https://www.olofsimren.com/kill-sessions-in-dynamics-nav/
Kill Sessions in Dynamics NAV (from NAV Page)
Source https://www.olofsimren.com/kill-sessions-in-dynamics-nav/
Poweshell Solution
Stopping Idle Microsoft Dynamics NAV Services
Nice Sessions Killing !
Pingback: “How -to” restrict multiple logins for same user in NAV | Roberto Stefanetti NAV Blog
Pingback: “How -to” restrict multiple logins for same user in NAV - Roberto Stefanetti's NAV Blog - Dynamics NAV Users - DUG
This is very intereѕting, You arre a very skilled blogger.
Ӏ have joined yoᥙr rrss feed and look forward t᧐ seeking mօгe of your excellent post.
Also, I’ve shared ʏoսr website іn my social networks!
Hi
Can you check and fix web links on your webpage?
Most of them are not working..
Especially those links that are related to Github and compiled Session-Killer-3.0-EXE.
Regards