ReleaseSQLServerMemory.sql

/*
Purpose:
- Demonstrate a temporary max-server-memory reduction while preserving the current setting.

Safety:
- Changes server configuration only when @Execute = 1; the default returns a review plan.
- Reducing memory can cause cache eviction and severe performance degradation.
- Prefer diagnosing memory pressure instead of using this as routine maintenance.

Requirements:
- SQL Server 2012 or later.
- ALTER SETTINGS permission.

Customization:
- Set @TemporaryMaxMemoryMB only after checking host memory and current SQL Server usage.
*/

SET NOCOUNT ON;

DECLARE @TemporaryMaxMemoryMB INT = 10240;
DECLARE @Execute BIT = 0;
DECLARE @CurrentMaxMemoryMB INT;

SELECT @CurrentMaxMemoryMB = CONVERT(INT, value_in_use)
FROM sys.configurations
WHERE name = N'max server memory (MB)';

SELECT
    @CurrentMaxMemoryMB AS current_max_memory_mb,
    @TemporaryMaxMemoryMB AS temporary_max_memory_mb,
    N'EXEC sys.sp_configure ''max server memory (MB)'', '
        + CONVERT(NVARCHAR(20), @TemporaryMaxMemoryMB) + N'; RECONFIGURE;' AS reduce_command,
    N'EXEC sys.sp_configure ''max server memory (MB)'', '
        + CONVERT(NVARCHAR(20), @CurrentMaxMemoryMB) + N'; RECONFIGURE;' AS restore_command;

IF @Execute = 1
BEGIN
    EXEC sys.sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sys.sp_configure 'max server memory (MB)', @TemporaryMaxMemoryMB;
    RECONFIGURE;

    PRINT 'Memory limit reduced. Restore it explicitly after completing the diagnostic work.';
    PRINT 'Previous max server memory (MB): ' + CONVERT(VARCHAR(20), @CurrentMaxMemoryMB);
END;