SQL Script

SQL Sessions Killer 2.0


SQL Sessions Killer 2.0
Sessionkille2r
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.
SP_SQL_KillNavServerSession
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
SP_SQL_KillNoNavSession
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
Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...