SQL Sessions Killer 2.0

If you have some issues about “there are no more NAV license availables.. you can’t login” and issues seem caused by multiple sessions consumed by NAS and NAV Users (user with multilple logins not closed etc..), you can create and schedule these SQL stored procedure to kill inactive NAS & Inactive Users Sessions.

Kill SQL stored procedures :
– SP_SQL_KillNavServerSession
– SP_SQL_KillNoNavSession

Warning ! check your case before use this solution !  ..may be unnecessary.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SP_SQL_KillNavServerSession] */
/* Script Date: 13/10/2015 15:06:43 by RS******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— =============================================
— Author:  Roberto Stefanetti
— Create date: 13/10/2015
— Description: KILL ALL NAV ACTIVE USER SESSSIONS WITH MULTIPLE INSTANCES, KEEP THE FIRST
— =============================================
ALTER 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 [DB].[dbo].[Active Session]
WHERE [User ID] = ‘DOMAIN\USER’ (example NAV Service User)
group by [Session ID])

 — Remove Active Session
DELETE FROM [DB].[dbo].[Active Session] where [Session ID]>@MaxIndex AND [User ID] = ‘DOMAIN\USER’  (example NAV Service User)
END

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SP_SQL_KillNoNavSession]  */
/* Script Date: 13/10/2015 15:13:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Roberto Stefanetti, Repacked from Opensource MSDN Published

Script
— Create date: 13-10-2015
— Description: Kill No Nav Sessions sleeping (example sleep for 15 minutes, with0 Open transactions)
— =============================================
ALTER 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)
);

DECLARE @session_time as int;
DECLARE @vSID as int;
DECLARE @command as varchar(MAX);
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
—————————-

set @session_time=15; — assign session time permitted (example 15 minute)

— Assign List of Users who WILL NOT BE affected by this SESSION KILL
— <> USERS > EXCLUSION LIST
INSERT into @NotAffectedUserList SELECT ‘sa’
INSERT into @NotAffectedUserList SELECT ‘intranet_service’
INSERT into @NotAffectedUserList SELECT ‘NT AUTHORITY\SERVIZIO DI RETE’
INSERT into @NotAffectedUserList SELECT ‘NT AUTHORITY\SYSTEM’
INSERT into @NotAffectedUserList SELECT ‘NASJob’
INSERT into @NotAffectedUserList SELECT ‘NAVSRV’
… etc. etc. etc.

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  –read from SQL Server running sessions
WHERE
open_transaction_count = 0  — Only open transactions > COUNT = 0
AND host_name <> ‘NAVSRV’ COLLATE Latin1_General_CI_AS     –> example filter SERVER NAME <> NAV SERVERS (NAVSRV)
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;

WHILE( @RowIndex <= @MaxIndex)
BEGIN
SELECT @vSID = sid
FROM @Session
WHERE id = @RowIndex;

 — String together the KILL statement
SELECT @command = ‘KILL ‘ + CAST(@vSID AS varchar(5))  — KILL SESSION

 BEGIN TRY

  — Execute the final string to KILL the spids
EXEC (@command)

 END TRY
BEGIN CATCH
declare @emsg varchar(500);
SELECT
@emsg=ERROR_MESSAGE();

  print @emsg;
END CATCH

 SET @RowIndex = @RowIndex + 1;
END;
END