Shrink transaction log files in SQL Server

Shrink Transaction Logs.sql

IF OBJECT_ID('tempdb.dbo.#CommandQueue', 'U') IS NOT NULL
BEGIN
  DROP TABLE #CommandQueue
END

CREATE TABLE #CommandQueue
(
    ID INT IDENTITY (1, 1)
    , SqlStatement VARCHAR(1000)
)

INSERT INTO #CommandQueue(SqlStatement)
SELECT 'USE [' + A.[name] + '] DBCC SHRINKFILE (N''' + B.[name] + ''')'
FROM sys.databases A
    INNER JOIN sys.master_files B
    ON A.database_id = B.database_id
WHERE
    A.[name] IN
    (
        -- List all databases that are PRIMARY or local only and exclude
        -- forwarder databases
        --
        -- Reference: https://dba.stackexchange.com/a/210253

        SELECT adc.[database_name]
        FROM sys.availability_groups ag 
            INNER JOIN sys.dm_hadr_availability_replica_states ars
            ON ars.group_id = ag.group_id
            INNER JOIN sys.availability_databases_cluster adc
            ON adc.group_id = ag.group_id
        WHERE ars.is_local = 1
            AND ars.role_desc = 'PRIMARY'
            AND NOT EXISTS (
                SELECT 1
                FROM sys.availability_groups dag 
                    INNER JOIN sys.availability_replicas fwd
                    ON fwd.group_id = dag.group_id
                    INNER JOIN sys.availability_groups ag2
                    ON ag2.[name] = fwd.replica_server_name
                    INNER JOIN sys.availability_databases_cluster db
                    ON db.group_id = ag2.group_id
                WHERE dag.is_distributed = 1
                    AND db.[database_name] = adc.[database_name]
        )
        UNION ALL 
        SELECT [name]
        FROM sys.databases d 
        WHERE NOT EXISTS (
            SELECT 1 
            FROM sys.availability_databases_cluster adc
            WHERE adc.[database_name] = d.[name]
        )
    )
    AND B.[type] = 1 -- log file

DECLARE @id INT

SELECT @id = MIN(ID)
FROM #CommandQueue

WHILE @id IS NOT NULL
BEGIN
    DECLARE @sqlStatement VARCHAR(1000)
    
    SELECT @sqlStatement = SqlStatement
    FROM #CommandQueue
    WHERE ID = @id

    PRINT 'Executing ''' + @sqlStatement + '''...'

    EXEC (@sqlStatement)

    DELETE FROM #CommandQueue
    WHERE ID = @id

    SELECT @id = MIN(ID)
    FROM #CommandQueue
END

DROP TABLE #CommandQueue