一、查詢阻塞和被阻塞的會話

二、找出阻塞的具體SQL

三、編寫C#程序，每隔10秒監控SQL Server數據庫中的阻塞會話，定位出阻塞的根源會話并終止它們，同時記錄日志。

using System;
using System.Data.SqlClient;
using System.IO;
using System.Timers;


class Program
{
    private static Timer timer;
    private static string connectionString = "your_connection_string_here";


    static void Main(string[] args)
    {
        timer = new Timer(10000); // 每10秒執行一次
        timer.Elapsed += CheckForBlockingSessions;
        timer.AutoReset = true;
        timer.Enabled = true;


        Console.WriteLine("Press [Enter] to exit the program.");
        Console.ReadLine();
    }


    private static void CheckForBlockingSessions(object source, ElapsedEventArgs e)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();


                string query = @"
                SELECT 
                    r.session_id AS BlockedSessionID,
                    r.blocking_session_id AS BlockingSessionID,
                    r.text AS SqlText
                FROM sys.dm_exec_requests AS r
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS r
                WHERE r.blocking_session_id <> 0;";


                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int blockedSessionId = reader.GetInt32(0);
                            int blockingSessionId = reader.GetInt32(1);
                            string sqlText = reader.GetString(2);


                            LogBlockingSession(blockedSessionId, blockingSessionId, sqlText);
                            KillSession(blockingSessionId);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogError(ex.Message);
        }
    }


    private static void KillSession(int sessionId)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string killQuery = $"KILL {sessionId};";
                using (SqlCommand killCommand = new SqlCommand(killQuery, connection))
                {
                    killCommand.ExecuteNonQuery();
                    LogKillSession(sessionId);
                }
            }
        }
        catch (Exception ex)
        {
            LogError($"Failed to kill session {sessionId}: {ex.Message}");
        }
    }


    private static void LogBlockingSession(int blockedSessionId, int blockingSessionId, string sqlText)
    {
        string logMessage = $"[{DateTime.Now}] Blocked Session ID: {blockedSessionId}, Blocking Session ID: {blockingSessionId}, SQL Text: {sqlText}";
        File.AppendAllText("blocking_sessions.log", logMessage + Environment.NewLine);
        Console.WriteLine(logMessage);
    }


    private static void LogKillSession(int sessionId)
    {
        string logMessage = $"[{DateTime.Now}] Killed Session ID: {sessionId}";
        File.AppendAllText("killed_sessions.log", logMessage + Environment.NewLine);
        Console.WriteLine(logMessage);
    }


    private static void LogError(string message)
    {
        string logMessage = $"[{DateTime.Now}] Error: {message}";
        File.AppendAllText("errors.log", logMessage + Environment.NewLine);
        Console.WriteLine(logMessage);
    }
}

說明

1. 連接字符串：替換 your_connection_string_here 為實際的數據庫連接字符串。

2. 定時器：使用 System.Timers.Timer 類設置每10秒執行一次檢查。

3. 檢查阻塞會話：在 CheckForBlockingSessions 方法中，查詢阻塞會話和根源會話的信息。

4. 終止會話：在 KillSession 方法中，執行 KILL 命令來終止阻塞會話。

5. 日志記錄：日志記錄包括阻塞會話的詳細信息和終止會話的操作，以及錯誤信息。

注意事項

• 運行此程序需要確保有足夠的權限來訪問數據庫和執行 KILL 命令。

• 請仔細測試程序以確保其符合預期行為，尤其是在生產環境中。

• 日志文件的路徑和權限需要根據實際情況進行配置。

轉自https://www.cnblogs.com/tianqing/p/18217020 作者：Eric Zhou