Get or Set SQL Max Memory using PowerShell

Gets, Sets and Recommends SQL Max Memory.

 
 
 
 
 
4.7 Star
(7)
2,306 times
Add to favorites
Databases
5/22/2015
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Thanks, Modified
    1 Posts | Last post June 17, 2016
    • Chrissy,
      
      Thanks for the Share! Worked like a charm. Added the following coped Get/Set-SQLMaxMemory and changed Max to Min to also include the ability to set min memory...
      
      
      Thanks again!
      
      Justin
  • Setting up SQL Maximum Memory Size
    6 Posts | Last post April 08, 2016
    • Regarding setting the SQL server max memory :
      I tried doing this but it fails to change the configuvalue
      
      $sStrSQLServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') localhost
      $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
      $sStrSQLServer.Alter()
      
      So I tried below 
      
      $sStrSQLServer =  New-Object Microsoft.SqlServer.Management.Smo.Server localhost
          $sStrSQLServer.ConnectionContext.LoginSecure=$false; 
          $sStrSQLServer.ConnectionContext.set_Login($sStrSAUserAccountName)
          $sStrSQLServer.ConnectionContext.set_Password($sStrSAUserPassword)  
      
          try 
          { 
              $sStrSQLServer.ConnectionContext.Connect()         
              $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
              $sStrSQLServer.Configuration.Alter()
          }  
      
      But above fails with error as Exception setting "ConfigValue":Failed to retrieve data for the request
      
      What am I missing above
    • Hey GGun,
      Can you confirm that your account has access to make the changes by doing it first using SQL Server Management Studio? If so, please paste your full PowerShell exception (well, the useful parts ;).
    • I used the sa account to login to SSMS manually and it worked without issue.I used same sa and password to login to SSMS using powershell the powershell throw error when the execution reaches at this point  $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
      
      And I get the exception -  Exception setting "ConfigValue":Failed to retrieve data for the request
      
      I don't get any other exception. Is there anyother way to find other exception from powershell.
      
    • Sure, I will add all the images of my execution result and highlight the issue. I will upload my images sooner.
      
    • I don't understand what is wrong - The code works sometimes and it fails something. I don't know behave so. It fails with same exception as mentioned above I don't find any other exception. I can't attach my code as it exceed the limit required for this textbox. but it is pretty straight forward.
      I am Installing the SQL through the powershell script then I add the SMO .dll to assembly and then I change the memory and it fails in setting the ConfigValue. No idea why
    • I can see that when I run the powershell that does .Net Framework Installation,SQL installation , Adding Assembly and then Change the memory limit it fails for the first time ,but when I just rerun the change memory limit second time it runs without any issue.
      Do I need to restart the machine and then run the  change memory limit script? I checked the evetvwr I couldn't find any error  there too
  • Very Nice!
    1 Posts | Last post February 16, 2016
    • Thank you!  I've been pulling my hair out doing start-process with sqlcmd.exe and invoke-sqlcmd and stuffing SQL code through the pipeline, etc.  This is much better.