Setting Min and Max SQL Server Memory Dynamically

Based off a standard configuration the goal is to set the min and max memory values via TSql script code.

You need to create the sp_configure statements as nvarchar variable because the syntax is incorrect when trying to execute them as part of a logic check


 DECLARE @totalmemory BIGINT
 DECLARE @max_memorysqlstmt NVARCHAR(500)
 DECLARE @min_memorysqlstmt NVARCHAR(500)
 SET @totalmemory = (SELECT CAST(physical_memory_kb as bigint) / POWER(1024, 2) FROM sys.dm_os_sys_info)
 IF @totalmemory > = 256
 BEGIN
PRINT 'Setting SQL Server memory to 256GB'
SET @min_memorysqlstmt = 'sp_configure ''min server memory'', 256000;'
SET @max_memorysqlstmt = 'sp_configure ''max server memory'', 256000;'
 END
 ELSE
 BEGIN
PRINT 'Setting SQL Server memory to 10GB'
SET @min_memorysqlstmt = 'sp_configure ''min server memory'', 10240;'
SET @max_memorysqlstmt = 'sp_configure ''max server memory'', 10240;'
 END
 EXECUTE sp_executesql @max_memorysqlstmt
  EXECUTE sp_executesql @min_memorysqlstmt
 RECONFIGURE;