USE [MO]
GO
/****** Object:  StoredProcedure [dbo].[sp_KillOldSessions]    Script Date: 01.11.19 15:21:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_KillOldSessions]
    -- Add the parameters for the stored procedure here
    @IdleTime int= 14400000
AS
BEGIN
    SET NOCOUNT ON;

    --declare @IdleTime int;
    --set @IdleTime = 10800000
    declare @app varchar(250);
    declare @app1 varchar(250);
    declare @spid varchar(250);
    declare @kill_spid varchar(250);

    set @app='МО%2%'

    SELECT
        [Connection ID]
    INTO #t
    FROM (
        SELECT
            CAST(SP.[spid] AS INTEGER) AS "Connection ID"
            ,CAST(RTRIM(SP.[loginame]) AS NVARCHAR(64)) COLLATE Cyrillic_General_CI_AS AS "User ID"
            ,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"
            ,CAST(RTRIM(SP.[hostname]) AS NVARCHAR(64)) COLLATE Cyrillic_General_CI_AS AS "Host Name"
            ,CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time"
            ,CAST (SD.[name] AS NVARCHAR(128)) COLLATE Cyrillic_General_CI_AS AS "Database Name"
        FROM            [master].[dbo].[sysprocesses] AS SP
        JOIN            [master].[dbo].[sysdatabases] AS SD
        ON (SP.[dbid] = SD.[dbid])
        WHERE           SP.[ecid] = 0 AND SP.[last_batch] >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120)
    ) as t
    where   [Database Name] = DB_NAME()
            and [Connection ID] > 50

            and [Application Name] like @app
            and [Idle Time] > @IdleTime


    DECLARE kills CURSOR FOR select * from #t

    OPEN kills;

    FETCH NEXT FROM kills INTO @spid
    WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @kill_spid='KILL ' + @spid
        print @kill_spid
        EXEC (@kill_spid)

        FETCH NEXT FROM kills INTO @spid;
    END;

    close kills
    DEALLOCATE kills
    drop table #t
END