March 20, 2014 at 12:40 pm
All,
Do I need a SQL reboot inorder to make the changes in effect, if I change the Max memory setting to a lower value?
Right now its set to default.
Environment: SQL 2008, Clustered
Many thanks.
March 20, 2014 at 1:09 pm
You will need to take the SQL Server service off line and bring it back online after making the setting change.
March 20, 2014 at 3:13 pm
NO, you do not.
You must at least RECONFIGURE, and maybe RECONFIGURE WITH OVERRIDE, so just use the latter, as that will cover all cases.
Note that it may take SQL a while to release RAM if you've lowered the amount. SQL does it gradually, not in a "panic".
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory', <#mb_of_ram_for_sql_to_use>;
RECONFIGURE WITH OVERRIDE;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2014 at 11:51 pm
No restart required. I've done it many times via management studio, memory tab of instance properties. After entering a max value and clicking ok you can see the used memory in task manager dropping gradually.
March 21, 2014 at 3:00 am
Adam Angelini (3/20/2014)
You will need to take the SQL Server service off line and bring it back online after making the setting change.
Incorrect. You do not. There few settings that require a restart and these are usually to pick up changes external to SQL itself.
ScottPletcher (3/20/2014)
NO, you do not.You must at least RECONFIGURE, and maybe RECONFIGURE WITH OVERRIDE, so just use the latter, as that will cover all cases.
Note that it may take SQL a while to release RAM if you've lowered the amount. SQL does it gradually, not in a "panic".
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory', <#mb_of_ram_for_sql_to_use>;
RECONFIGURE WITH OVERRIDE;
Correct answer. 😀
March 21, 2014 at 4:08 am
No restart is required. Nor do you need to use WITH OVERRIDE on the reconfigure statement. WITH OVERRIDE is for when you want to force invalid values for config settings, ones that SQL would reject otherwise. Setting memory to sensible values is not one of those cases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2014 at 4:17 am
No, You don't need to restart for setting max server memory.
sp_configure 'max server memory (MB)',your_valr
go
reconfigure with override
go
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply