Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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 '13 at 5:27
up vote 13 down vote accepted

Try this on the master database:

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'

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

share|improve this answer
That's it. Thank you! – token Jul 18 '12 at 17:45
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 '14 at 13:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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