.::: Set Limit Max Server Memory in SQL Server :::.

1. Check Memory server 

 

2. Capture before
SELECT @@servername as ServerName,[name],[value], [value_in_use], description
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' or
      [name] = 'min server memory (MB)' ;
 

3. Server properties > 2147483647 mb to 1536 mb > Memory, usually 70% from total memory server

4. configure
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'1536'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO
 

5. capture after
 SELECT @@servername as ServerName,[name],[value], [value_in_use], description
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' or
      [name] = 'min server memory (MB)' ;