What is Blocking in SQL, How to solve blocking, and Kill Session

CREATE PROCEDURE usp_FindAndResolveBlocking
    @DatabaseName SYSNAME,      -- Name of the database to check
    @BlockingSessionId INT = NULL   -- Optional: Session ID to terminate
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Display blocking sessions using sp_who2
   
    EXEC sp_who2;

    -- Step 2: Display the oldest active transaction in the specified database
    DBCC OPENTRAN(@DatabaseName);

	declare @SessionID int = (Select top 1 blocking_session_id from sys.dm_exec_requests WHERE blocking_session_id <> 0)

    -- Step 3: Display blocking details using DMVs
    PRINT 'Displaying details of blocked and blocking sessions:';
    SELECT 
        blocking_session_id AS BlockingSession, 
        session_id AS BlockedSession,
        wait_type, 
        wait_time / 1000 AS WaitTimeInSeconds, 
        wait_resource
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0;

	-- Check details of the Session:
	EXEC sp_who2 @SessionID;

	--Check details of the blocking transaction:

		DBCC INPUTBUFFER(@SessionID);

    -- Step 4: Display the input buffer for a specified blocking session ID
    IF @BlockingSessionId IS NOT NULL
    BEGIN
        PRINT 'Displaying the input buffer for the specified blocking session:';
        DBCC INPUTBUFFER(@BlockingSessionId);
        
        -- Step 5: Terminate the blocking session if specified
        PRINT 'Terminating the blocking session:';
		DECLARE @sqlCmd VARCHAR(MAX)
		SET @sqlCmd = ''
        SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(@SessionID AS VARCHAR)
		EXEC(@sqlCmd)
    END
    ELSE
    BEGIN
        PRINT 'No blocking session ID specified for termination.';
    END
END;
GO

--EXEC usp_FindAndResolveBlocking 'AdventureWorksDW2022'

CREATE PROCEDURE usp_BlockingTree
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Process details with waits
    SELECT 
        R.spid AS SPID,
        R.blocked AS BLOCKED,
        R.waittime / 1000.0 AS WaitTimeSec,         -- Wait time in seconds
        R.lastwaittype AS WaitType,                 -- Type of wait (LCK_M_X etc.)
        R.loginame AS LoginName,                    -- User
        R.hostname AS HostName,                     -- Machine name
        DB_NAME(R.dbid) AS DBName,                  -- Database name
        REPLACE(REPLACE(T.TEXT, CHAR(10), ' '), CHAR(13), ' ') AS BatchText
    INTO #tmpProcess
    FROM sys.sysprocesses R
    CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T;

    -- Step 2: Recursive CTE for blocking chain
    WITH BlockerTree (SPID, BLOCKED, LEVEL, WaitTimeSec, WaitType, LoginName, HostName, DBName, BatchText)
    AS
    (
        -- Root blockers
        SELECT 
            SPID,
            BLOCKED,
            CAST(RIGHT('0000' + CAST(SPID AS VARCHAR), 4) AS VARCHAR(1000)) AS LEVEL,
            WaitTimeSec,
            WaitType,
            LoginName,
            HostName,
            DBName,
            BatchText
        FROM #tmpProcess R
        WHERE (BLOCKED = 0 OR BLOCKED = SPID)
          AND EXISTS (SELECT 1 FROM #tmpProcess R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

        UNION ALL

        -- Children (blocked processes)
        SELECT 
            R.SPID,
            R.BLOCKED,
            CAST(BlockerTree.LEVEL + RIGHT('0000' + CAST(R.SPID AS VARCHAR), 4) AS VARCHAR(1000)),
            R.WaitTimeSec,
            R.WaitType,
            R.LoginName,
            R.HostName,
            R.DBName,
            R.BatchText
        FROM #tmpProcess R
        INNER JOIN BlockerTree ON R.BLOCKED = BlockerTree.SPID
        WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
    )

    -- Step 3: Final output
    SELECT 
        CASE 
            WHEN LEN(LEVEL)/4 - 1 = 0 THEN 'ROOT BLOCKER --> ' 
            ELSE REPLICATE('   ', LEN(LEVEL)/4 - 1) + '|--- ' 
        END
        + CAST(SPID AS NVARCHAR(10)) AS BlockingTree,

        BLOCKED,
        WaitTimeSec,
        WaitType,
        LoginName,
        HostName,
        DBName,
        BatchText
    FROM BlockerTree
    ORDER BY LEVEL;

    DROP TABLE #tmpProcess;
END;
GO

-- Run it
EXEC usp_BlockingTree;
 

--Kill All sessions

use master
go
 
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)
 
SET @sqlCmd = ''
SET @dbName = 'dbname' -- Change database name here
 
SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
         CHAR(13)
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName
 
PRINT @sqlCmd
 
--EXEC (@sqlCmd)