PowerShell.com
Library » Script Library » SQL Server » Get-MSSQL-MaxMemory

Get-MSSQL-MaxMemory

Previous | Next | View all files | View Slideshow
posted by Richard Giles
10-06-2008
Downloads: 467
File size: 2.4kB
Views: 1,431
Embed
Get-MSSQL-MaxMemory
  1. ## ===================================================================== 
  2. ## Title       : Get-MSSQL-MaxMemory 
  3. ## Description : Get max memory property from SQL Server 
  4. ## Author      : Idera 
  5. ## Date        : 9/1/2008 
  6. ## Input       : -serverInstance    <server\instance> 
  7. ##                   -verbose  
  8. ##                   -debug     
  9. ## Output      : Integer in MB,  
  10. ##                     -1 if memory is unlimited, 
  11. ##                      -2 if no connection to the desired DB can be obtained 
  12. ## Usage            : PS> . Get-MSSQL-MaxMemory -serverInstance MyServer -v -d 
  13. ## Notes            : Adapted from Jakob Bindslet script 
  14. ## Tag            : SQL Server, ADO, Configuration, Memory 
  15. ## Change log  : 
  16. ## ===================================================================== 
  17.  
  18. param 
  19. ( 
  20.       [string]$serverInstance = "local", 
  21.     [switch]$verbose, 
  22.     [switch]$debug 
  23. ) 
  24.  
  25. function main() 
  26. { 
  27.     if ($verbose) {$VerbosePreference = "Continue"} 
  28.     if ($debug) {$DebugPreference = "Continue"} 
  29.     Get-MSSQL-MaxMemory $serverInstance 
  30. } 
  31.  
  32. function Get-MSSQL-MaxMemory($serverInstance) 
  33. { 
  34.     trap [Exception]  
  35.     { 
  36.         write-error $("TRAPPED: " + $_.Exception.Message); 
  37.         continue; 
  38.     } 
  39.  
  40.     #initialize ADO connection parameters 
  41.     Write-Debug "Initializing connection parameters..." 
  42.     $adoOpenStatic = 3 
  43.     $adoLockOptimistic = 3 
  44.     $timeout = 3 
  45.  
  46.     #get memory configuration setting from sysconfigures system table 
  47.     # TIP: using ADO to run a query 
  48.     Write-Debug "Connecting to server: $serverInstance" 
  49.     $adoConnection = New-Object -comobject ADODB.Connection 
  50.     $adoRecordset = New-Object -comobject ADODB.Recordset 
  51.     $query = "SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544" 
  52.     $adoConnection.Set_ConnectionString("Provider=SQLOLEDB; Data Source=" + $srv + "; Initial Catalog=master; Integrated Security=SSPI") 
  53.     $adoConnection.Set_ConnectionTimeout($timeout) 
  54.     $adoConnection.Open() 
  55.  
  56.     if ($adoConnection.State -eq "1")  
  57.     { 
  58.         Write-Debug "Connection succeeded..." 
  59.        $adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic) 
  60.        $adoRecordset.MoveFirst() 
  61.        $maxMemory = ($adoRecordset.Fields.Item("value").Value) 
  62.        if ($maxMemory -eq 2147483647)  
  63.        { 
  64.             Write-Verbose "Max memory is set to unlimited" 
  65.           $maxMemory = -1 
  66.        }  
  67.        $adoRecordset.Close() 
  68.        $adoConnection.Close() 
  69.     }  
  70.     else  
  71.     { 
  72.         Write-Debug "Connection failed..." 
  73.        $maxMemory = -2 
  74.     } 
  75.     Write-Debug "Max memory is set to $maxMemory" 
  76.     Write-Output $maxMemory 
  77. } 
  78.  
  79. main 
Filed under: , ,

Get max memory property from SQL Server

Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.