PowerShell: Automatically Fix SQL Server by Killing Long Running Queries

# autofixSqlLongRunningQueries.ps1
# version 0.01
# This version is limited to SQL Server Localhost execution, Not Remote [yet]

# Kill session_id basing on these variables
$sqlServername=$env:computername
$maxBlockDuration=3600000 # 1 hour = 3600000 ms
$maxRunningDuration=3600000
$excludeKeywords='BACKUP','ROLLBACK'

function autofixSqlLongRunningQueries{
    param(
        $sqlServername=$env:computername,
        $maxBlockDuration=3600000,
        $maxRunningDuration=3600000,
        $excludeKeywords=@('BACKUP','ROLLBACK')
    )
    $checkRunningQueries="
    USE Master
    SELECT s.TEXT,r.total_elapsed_time,r.session_id,r.status,r.command
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) s
    " 
    $checkBlockingQueries="
        USE Master
        GO
        SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
        FROM sys.dm_os_waiting_tasks 
        WHERE blocking_session_id <> 0
        GO
        "
    function killSqlSession($sessionId){
        return "
        USE Master
        GO
        KILL $sessionId
        "
    }
    $runningQueries=try{
            $result=Invoke-Sqlcmd -ServerInstance $sqlServername -Query $checkRunningQueries
            $result|?{$_.total_elapsed_time -ge $maxRunningDuration -and $($excludeKeywords|?{$_.TEXT -notmatch $_}) }
        }catch{write-warning $_}
    $blockingQueries=try{
            $result=Invoke-Sqlcmd -ServerInstance $env:computername -Query $checkBlockingQueries
            $result=|?{$_.wait_duration_ms -ge $maxBlockDuration}
        }catch{write-warning $_}
    $sessionsToKill=$runningQueries.session_id+$blockingQueries.blocking_session_id
    foreach($sessionId in $sessionsToKill){
        $killSessionCommand=killSqlSession $sessionId
        write-host $killSessionCommand
        # pause
        Invoke-Sqlcmd -ServerInstance $sqlServername -Query $killSessionCommand
    }
}

autofixSqlLongRunningQueries $sqlServername `
    $maxBlockDuration `
    $maxRunningDuration `
    $excludeKeywords

Leave a Reply

Your email address will not be published. Required fields are marked *