Dynamically limit MSSQL memory max size to a reasonable size

-- If your SQL server runs on the same machine as many other things, but tends to steal too much memory, run this script every month or week to set the max amount of memory to a large but more reasonable size.

EXEC sp_configure 'show advanced options', 1;
GO

DECLARE @physmem INT = (SELECT total_physical_memory_kb FROM sys.dm_os_sys_memory);
DECLARE @availmem INT = (SELECT available_physical_memory_kb FROM sys.dm_os_sys_memory);
DECLARE @suggested_sql_memory_mb INT = (@availmem*3 + @physmem) / 7 / 1024;
SELECT @physmem/1024/1024 AS [Physical memory, GB], @availmem/1024/1024 AS [Available, GB], @suggested_sql_memory_mb/1024 AS [Suggested max memory, GB];
EXEC sp_configure 'max server memory', @suggested_sql_memory_mb;
RECONFIGURE;