Thursday, March 10, 2011

Set Max memory on SQL Server

Always set max server memory option to maximum physical memory of the server

exec sp_configure 'min server memory (MB)', 500
go

-- Error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'min server memory (MB)' does not exist, or it may be an advanced option.

-- Solution
exec sp_configure 'show advanced options', 1
go
RECONFIGURE
go
exec sp_configure 'min server memory (MB)' ,500
go
exec sp_configure 'max server memory (MB)' , 'your server physical memory'
go