管理数据库的睡眠(Sleeping)连接,超过总连接数时,按序清理睡眠(Sleeping)连接

---- Exec MQTrcOverSleepingAutoKillSPID 650
Create procedure MQTrcOverSleepingAutoKillSPID 
  (@nMaxSleeping as int --允许的最大睡眠连接数
    )  as 
Begin

    Declare @iStartTime as datetime 
    Declare @iminTime as datetime
    Declare @icSQLSTring as varchar(50)
    
    select     'HTSAC' as [DBName]
        ,spid
         ,'' as [TextData]
         ,0 as [Duration]
         ,login_time as login_time
         ,last_batch as EndTime
         ,physical_io as reads
         ,'' as RowCounts
         ,CPU
         ,cast(loginame as nvarChar(18)) as loginame
        ,Cast([HostName] as nvarchar(16)) as [HostName]
        ,DATEDIFF(ss,last_batch,GETDATE()) as nTime
        ,Year(last_batch)*100+MONTH(last_batch)  as nYM
        ,'' as cTrcFile
        ,[SYSPROCESSES].cmd
        ,status
        into #tmp_ReadTrack_
     FROM
    [Master].[dbo].[SYSPROCESSES] WHERE 
      status ='sleeping'  And not loginame IN('sa','FSHT\htbf')
     And datepart(hh,last_batch)<=19            --只管理工作时间内的连接
    And datepart(hh,last_batch)>=8

    -- 对睡眠的连接进行排序并编码
    SELECT ROW_NUMBER() OVER(ORDER BY EndTime)    as nID,* into #tmp_ReadTrack
        From (Select  TOp 10000
                    SPID,MAX([TextData]) as [TextData],Min(login_time) as login_time,Max(EndTime) as EndTime,MAX(CPU) as CPU,Max(status) as status,Max(cMD) as cMD 
                    ,Max([HostName]) as [HostName],Max(loginame) as loginame
                FROM #tmp_ReadTrack_
                Where 1=1 Group BY SPID Order by EndTime 
            ) A
            Order by EndTime
        
        

    Drop table #tmp_ReadTrack_

    Declare @nSumSPID as int,@nSumSleeping as int
    Declare @nOddSleeping as int
        Select @nSumSPID = Count(*) From [Master].[dbo].[SYSPROCESSES]        --取目前所有连接的进程数
    Select @nSumSleeping = Count(*) From #tmp_ReadTrack                                  --取目前睡眠的连接数
        Select @nSumSPID as 当前服务器总线程数,@nSumSleeping as 当前睡眠总数,@nMaxSleeping as 最大数,*
        From #tmp_ReadTrack

    IF @nSumSPID > @nMaxSleeping        --当目前所有连接的进程数 大于 允许的最大进程数时才处理
    Begin
         
         --目前所有连接的进程数 减去 允许的最大进程数时才处理,算出要处理的睡眠的连接数
        set @nOddSleeping = @nSumSPID - @nMaxSleeping    

        -- 显示要处理的睡眠的连接数
        Select   * From #tmp_ReadTrack         Where nID <= @nOddSleeping     Order by nID 

           
 
        -- 把要处理的睡眠的连接数放入游标
        DECLARE c_TrctimeOutX CURSOR FOR      
            SELECT SPID,MAX([TextData]) as [TextData],Min(EndTime) as EndTime,MAX(CPU) as CPU,Max(cMD) as cMD 
                FROM #tmp_ReadTrack 
                Where  nID <= @nOddSleeping Group BY SPID
     
        DECLARE @SPID as int,@RUNSQL as varchar(max),@StTime as datetime,@NewRunSQL as varchar(Max),@CPU as int,@cMD as varchar(100)
 
        OPEN c_TrctimeOutX
        FETCH NEXT FROM c_TrctimeOutX INTO @SPID,@RUNSQL,@StTime,@CPU,@cMD
        WHILE (@@FETCH_STATUS = 0)
        BEGIN    
            
            set @icSQLSTring = 'Kill ' + cast(@SPID as varchar(12))
            -- KILL 睡眠连接
            Exec (@icSQLSTring)    
 
            Print @icSQLSTring+' 完成!'
  
            FETCH NEXT FROM c_TrctimeOutX INTO @SPID,@RUNSQL,@StTime,@CPU,@cMD
        End            
        
        /* 保存处理日志
         insert into dbo.MQTRCOutTimeKill( [DBName]
            ,[TextData]
            ,[Duration]
            ,[StartTime]
            ,EndTime
            ,reads
            ,RowCounts
            ,CPU
            ,[LoginName]
            ,[HostName]
            ,nTime
            ,nYM
            ,cTrcFile
        )VALUES('SYS','已执行一次睡眠进程清理[当前服务器总线程数:'+cast(@nSumSPID as varchar(12))+' 当前睡眠总数:'+CAST(@nSumSleeping as varchar(20))+' 允许最大进程数:'+cast(@nMaxSleeping as varchar(20)),0,GETDATE(),0,0,0,0,'sys','sys',0,0,'')
        */
        CLOSE c_TrctimeOutX
        DEALLOCATE c_TrctimeOutX    
        
        
    End
 
    Drop table #tmp_ReadTrack
    
    Print '执先完成! MQ'
     
    
End