Here is a script that allow you to set the max server memory of your instance automatically. The calculation of the amount of memory to set for the instance is based on the amount of physical memory available on the server – 2Go (for the system).
/*======================================================== -- Automatically set the max server memory -- for the current SQL Server Instance -- with the following calculation: -- -- Max Server Memory = Physical Memory - 2Go ---------------------------------------------------------- -- -- Author: Arnaud Degraeve -- Creation Date: 2012-12-12 -- Modification Date: 2012-12-12 -- --======================================================*/ ---------------------------------------------------------- -- Variables Declaration ---------------------------------------------------------- DECLARE @strCommand AS VARCHAR(250) DECLARE @Max_Server_Memory_Mo AS INTEGER DECLARE @Sytem_memory_to_reserve AS INTEGER =2048 ---------------------------------------------------------- -- Find the physical memory available ---------------------------------------------------------- SELECT @Max_Server_Memory_Mo = (physical_memory_in_bytes / 1024 / 1024) - @Sytem_memory_to_reserve FROM sys.dm_os_sys_info ---------------------------------------------------------- -- Build the command to change the max server memory ---------------------------------------------------------- SET @strCommand = 'EXEC sys.sp_configure N''show advanced options'', N''1'' RECONFIGURE WITH OVERRIDE' + CHAR(10) SET @strCommand = @strCommand + 'EXEC sys.sp_configure '+ 'N''max server memory (MB)'', N''' + CONVERT(VARCHAR,@Max_Server_Memory_Mo) + '''' + CHAR(10) SET @strCommand = @strCommand + 'EXEC sys.sp_configure N''show advanced options'', N''0'' RECONFIGURE WITH OVERRIDE' ---------------------------------------------------------- -- Execute the command ---------------------------------------------------------- EXEC (@strCommand)