Often times, we need to release unused memory from SQL Server. SQL Server not releases unused memory immediately. One way to release the memory is to restart the SQL Server Service. This is not always possible in Production environment.
To release SQL Server unused memory in Production environments use the following commands by connecting the server using DAC (Dedicated Administrator Connection) Connection
Run the following commands to release the unused memory from SQL Server.
/*Use maximum of
10 GB of RAM and release rest of Memory*/
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'
102400'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
/*Run the following command after releasing the memory. Make sure you changed the Memory values based on the Server RAM (MAX 85%) */
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'298844'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
