Creates SQL Server Service Broker endpoint on port 4022 to reduce noise in the SQL Server error log (ERRORLOG). Includes port availability checks, conditional creation, logging, and optional code to stop and drop endpoint if needed.

ServiceBrokerLogNoiseReductionEndpoint.sql

/*
| File:        ServiceBrokerLogNoiseReductionEndpoint.sql
| Description: 🚦 Creates a Service Broker endpoint to reduce SQL Server log noise.
| Purpose:     Sets up a dedicated endpoint for Service Broker on a user-chosen
|              port (default 4022), with checks for port conflicts and robust
|              logging.  Useful for DBAs who want to keep error logs clean and
|              avoid noisy Service Broker errors.  Includes optional code to drop
|              the endpoint.  For test/dev use or advanced troubleshooting.  😎
| Created:     2024-05-21
| Modified:    2025-05-22
*/
-- AI-Updated: 2025-05-22 13:40:00 | Marker: AIUPD-20250522134000-5365727669636542726f6b65724c6f674e6f697365526564756374696f6e456e64706f696e742e73716c #|
--
-- Summary:
--   This script creates a Service Broker endpoint on a user-configurable port
--   (default 4022), with checks for port availability, existing endpoints, and
--   logs all actions.  It helps keep your SQL Server error log from being
--   flooded with Service Broker noise.  😅
--
-- Features:
--   - User-configurable port (set @EndpointPort at the top)
--   - Checks if the port is already in use
--   - Creates endpoint only if it does not exist
--   - Logs all actions and errors with xp_logevent
--   - Returns result sets for all execution paths
--   - Optional code to drop the endpoint (commented)
--   - Snarky comments and emoji for fun and clarity
--
-- Recommendations:
--   - Use in non-production or with caution in prod
--   - Monitor error logs for port conflicts
--   - Uncomment drop section only if you really mean it! ⚠️
--
-- Message Codes:
--   50001: Port is already in use
--   50002: Endpoint created and started successfully
--   50003: Failure to create/start endpoint
--   50004: Endpoint already exists
--   50005: Endpoint dropped (optional section)

-- =============================================
-- USER CONFIGURABLE PARAMETERS
-- =============================================
DECLARE @EndpointPort INT = 4022; -- Change this to your desired port number
DECLARE @EndpointName NVARCHAR(128) = 'ServiceBroker_LogNoiseReduction';
DECLARE @msg NVARCHAR(200);

-- Helper: Temp table for endpoints (not used, but left for future troubleshooting)
IF OBJECT_ID('tempdb..#Endpoints') IS NOT NULL DROP TABLE #Endpoints;
CREATE TABLE #Endpoints (
    name NVARCHAR(128),
    protocol_desc NVARCHAR(60),
    type_desc NVARCHAR(60),
    state_desc NVARCHAR(60),
    port INT
);
-- Define the endpoints query string once (not used, but left for future troubleshooting)
DECLARE @EndpointsQuery NVARCHAR(MAX) = N'SELECT name, protocol_desc, type_desc, state_desc, port FROM sys.tcp_endpoints WHERE type_desc = ''SERVICE_BROKER''';

-- Check if the chosen port is already in use by another endpoint
IF EXISTS (
    SELECT 1
    FROM sys.tcp_endpoints
    WHERE protocol_desc = 'TCP'
        AND port = @EndpointPort
        AND name <> @EndpointName
)
BEGIN
    SET @msg = 'User Msg 50001: Port ' + CAST(@EndpointPort AS NVARCHAR) + ' is already in use by another endpoint or service.';
    RAISERROR (@msg, 16, 1) WITH NOWAIT;
    EXEC xp_logevent 50001, @msg, error;
    SELECT 
        'PortInUse' AS Status,
        @msg AS Message,
        CAST(NULL AS NVARCHAR(128)) AS EndpointName,
        @EndpointPort AS Port;
    RETURN;
END

-- Check if endpoint already exists
IF EXISTS (
    SELECT 1
    FROM sys.tcp_endpoints
    WHERE name = @EndpointName
)
BEGIN
    SET @msg = 'User Msg 50004: Endpoint ServiceBroker_LogNoiseReduction already exists.';
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    EXEC xp_logevent 50004, @msg, informational;
    SELECT 
        'Exists' AS Status,
        @msg AS Message,
        name AS EndpointName,
        port AS Port
    FROM sys.tcp_endpoints
    WHERE name = @EndpointName;
    RETURN;
END

-- Create the endpoint if it doesn't exist
BEGIN TRY
    DECLARE @sql NVARCHAR(MAX) = N'CREATE ENDPOINT ServiceBroker_LogNoiseReduction
        STATE = STARTED
        AS TCP (LISTENER_PORT = ' + CAST(@EndpointPort AS NVARCHAR) + ', LISTENER_IP = ALL)
        FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)';
    EXEC sp_executesql @sql;

    -- Check that endpoint was successfully created and started
    IF EXISTS (
        SELECT 1
        FROM sys.tcp_endpoints
        WHERE name = @EndpointName
            AND state_desc = 'STARTED'
            AND port = @EndpointPort
    )
    BEGIN
        SET @msg = 'User Msg 50002: Endpoint ServiceBroker_LogNoiseReduction successfully created and started on port ' + CAST(@EndpointPort AS NVARCHAR) + '.';
        RAISERROR (@msg, 0, 1) WITH NOWAIT;
        EXEC xp_logevent 50002, @msg, informational;
        SELECT 
            'Success' AS Status,
            @msg AS Message,
            name AS EndpointName,
            port AS Port
        FROM sys.tcp_endpoints
        WHERE name = @EndpointName;
    END
    ELSE
    BEGIN
        SET @msg = 'User Msg 50003: Failed to create and start the endpoint ServiceBroker_LogNoiseReduction on port ' + CAST(@EndpointPort AS NVARCHAR) + '.';
        RAISERROR (@msg, 0, 1) WITH NOWAIT;
        EXEC xp_logevent 50003, @msg, error;
        SELECT 
            'Failure' AS Status,
            @msg AS Message,
            CAST(NULL AS NVARCHAR(128)) AS EndpointName,
            @EndpointPort AS Port;
    END
END TRY
BEGIN CATCH
    SET @msg = 'User Msg 50003: Failed to create and start the endpoint ServiceBroker_LogNoiseReduction on port ' + CAST(@EndpointPort AS NVARCHAR) + '. Error: ' + ERROR_MESSAGE();
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    EXEC xp_logevent 50003, @msg, error;
    SELECT 
        'Failure' AS Status,
        @msg AS Message,
        CAST(NULL AS NVARCHAR(128)) AS EndpointName,
        @EndpointPort AS Port;
END CATCH

/*

-- If you really want to drop the endpoint, here you go.  Use with care! 😬
DECLARE @msg NVARCHAR(200);
IF EXISTS (
    SELECT 1
    FROM sys.tcp_endpoints
    WHERE name = 'ServiceBroker_LogNoiseReduction'
)
BEGIN
    ALTER ENDPOINT ServiceBroker_LogNoiseReduction
        STATE = STOPPED;
    DROP ENDPOINT ServiceBroker_LogNoiseReduction;

    -- Confirm successful drop if endpoint no longer exists
    IF NOT EXISTS (
        SELECT 1
        FROM sys.tcp_endpoints
        WHERE name = 'ServiceBroker_LogNoiseReduction'
    )
    BEGIN
        SET @msg = 'User Msg 50005: Endpoint ServiceBroker_LogNoiseReduction has been dropped.';
        RAISERROR (@msg, 0, 1) WITH NOWAIT;
        EXEC xp_logevent 50005, @msg, informational;
        SELECT 
            'Dropped' AS Status,
            @msg AS Message,
            CAST(NULL AS NVARCHAR(128)) AS EndpointName,
            CAST(NULL AS INT) AS Port;
    END
END

-- Query existing Service Broker endpoints
-- (see above: now always returned after each outcome)
*/

made port user configurable, defaulting to port 4022
Used prefix User Msg 5000?: in messages

changed to return one row because I liked that better than only Messages output