MSSQL behaviour loves to eat RAM 🍪

 ⚠️ Understanding SQL Server Memory Management

• SQL Server Memory Usage: SQL Server does not immediately release memory even if tables are truncated or data is deleted.

• Memory Retention Mechanism: By design, SQL Server retains memory (buffer pool, plan cache) for performance reasons.

• Memory Clearing is Manual: Reducing SQL Server's memory usage must be done explicitly.

---

Solution: Clear SQL Server Memory Manually

To manually clear SQL Server memory and force it to release unused memory:

1. Clear the Buffer Cache

DBCC DROPCLEANBUFFERS;

2. Clear the Plan Cache

DBCC FREEPROCCACHE;

3. Clear Cache for Specific Database (Recommended)

USE [syncdb];
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;


Solution: Adjust Maximum Server Memory (Temporary Fix)
If SQL Server continues to consume too much memory, limit the maximum memory usage:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 4096; -- Set to 4GB (Adjust as needed)
RECONFIGURE;

This setting will prevent SQL Server from using more than the specified memory.

 Solution: Restart SQL Server Service (Last Resort)
If the above methods do not work, restarting the SQL Server service will force memory to be released:
Open SQL Server Configuration Manager.
Locate SQL Server (MSSQLSERVER) or your instance name.
Right-click and choose Restart.