SQL Server – Set Max Memory

SQL Server – Set Max Memory
MSDN Link on Changing Max Memory

/*
(4 row(s) affected)
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]
The configuration option 'max server memory' does not exist, or it may be an advanced option.
*/
-- SOLUTION

-- Enable Advanced Options
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- To chnage SQL Server Instance Max Memory
SELECT  @@SERVERNAME;

SELECT  *
FROM    sys.configurations
WHERE   name LIKE '%memory%';

-- Set the Max Memory to 30 Gig
EXEC sp_configure 'max server memory', 40960;
GO
RECONFIGURE;
GO

SELECT  *
FROM    sys.configurations
WHERE   name LIKE '%memory%';

-- Query for Memory Allocation
SELECT  ( physical_memory_in_use_kb / 1024 ) AS Memory_usedby_Sqlserver_MB ,
        ( locked_page_allocations_kb / 1024 ) AS Locked_pages_used_Sqlserver_MB ,
        ( total_virtual_address_space_kb / 1024 ) AS Total_VAS_in_MB ,
        process_physical_memory_low ,
        process_virtual_memory_low
FROM    sys.dm_os_process_memory;