16

I'm managing about 90 SQL Server instances and need a query to determine how much memory has been allocated to each instance. Please note, I'm not asking how to set it, just to see what it's currently set to without using the Management Studio.

Thanks for your help.

2
  • When executed this on my system. values is given as 2147483647 , my system RAM is only 2 GB, in this case how I consider return value as MB. if I conside it as MB then my system RAM should be 2097152 GB
    – user169898
    Apr 17, 2013 at 5:27
  • It is the configured value, not your actual RAM. It allows SQL Server to take up to this amount of memory (including virtual memory)
    – MichelZ
    May 4, 2018 at 12:39

1 Answer 1

29

Try this on the master database:

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

Gives you max server memory (MB) and min server memory (MB)

1
  • 1
    Just a note if you are using sqlcmd.exe: I'd recommend using the -W switch to trim whitespaces as described here - there are quite a lot of them (at least on my setup).
    – ChriPf
    May 22, 2014 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.