SHARE
TWEET

Untitled

bisonn Nov 1st, 2019 161 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [MO]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[sp_KillOldSessions]    Script Date: 01.11.19 15:21:17 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9.  
  10. ALTER PROCEDURE [dbo].[sp_KillOldSessions]
  11.     -- Add the parameters for the stored procedure here
  12.     @IdleTime int= 14400000
  13. AS
  14. BEGIN
  15.     SET NOCOUNT ON;
  16.    
  17.     --declare @IdleTime int;
  18.     --set @IdleTime = 10800000
  19.     declare @app varchar(250);
  20.     declare @app1 varchar(250);
  21.     declare @spid varchar(250);
  22.     declare @kill_spid varchar(250);
  23.  
  24.     set @app='МО%2%'
  25.    
  26.     SELECT  
  27.         [Connection ID]    
  28.     INTO #t
  29.     FROM (
  30.         SELECT        
  31.             CAST(SP.[spid] AS INTEGER) AS "Connection ID"
  32.             ,CAST(RTRIM(SP.[loginame]) AS NVARCHAR(64)) COLLATE Cyrillic_General_CI_AS AS "User ID"
  33.             ,CAST (RTRIM(SP.[program_name]) AS NVARCHAR(64)) COLLATE Cyrillic_General_CI_AS AS "Application Name",CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS "Login Type"
  34.             ,CAST(RTRIM(SP.[hostname]) AS NVARCHAR(64)) COLLATE Cyrillic_General_CI_AS AS "Host Name"
  35.             ,CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time"  
  36.             ,CAST (SD.[name] AS NVARCHAR(128)) COLLATE Cyrillic_General_CI_AS AS "Database Name"        
  37.         FROM            [master].[dbo].[sysprocesses] AS SP            
  38.         JOIN            [master].[dbo].[sysdatabases] AS SD
  39.         ON (SP.[dbid] = SD.[dbid])            
  40.         WHERE           SP.[ecid] = 0 AND SP.[last_batch] >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120)
  41.     ) as t
  42.     where   [Database Name] = DB_NAME()
  43.             and [Connection ID] > 50
  44.            
  45.             and [Application Name] like @app
  46.             and [Idle Time] > @IdleTime
  47.      
  48.    
  49.     DECLARE kills CURSOR FOR select * from #t
  50.    
  51.     OPEN kills;
  52.  
  53.     FETCH NEXT FROM kills INTO @spid
  54.     WHILE @@FETCH_STATUS = 0
  55.  
  56.     BEGIN
  57.  
  58.         SET @kill_spid='KILL ' + @spid
  59.         print @kill_spid
  60.         EXEC (@kill_spid)
  61.    
  62.         FETCH NEXT FROM kills INTO @spid;
  63.     END;
  64.  
  65.     close kills
  66.     DEALLOCATE kills
  67.     drop table #t  
  68. END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top