SQL Max server memory settings

Question

Post reply

SQL Max server memory settings

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
March 1, 2013 at 2:14 pm

#394294

How can I get a max server memory setting of SQL if it's not running/disalbled?
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Never tried it, but you could restore a backup of master with a different name to a running instance and issue:

SELECT *

FROM master.sys.configurations

WHERE name = 'max server memory (MB)';

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

SQLisAwe5oMe

SSChampion

Points: 14052

March 1, 2013 at 3:23 pm

#1592606

Thank you.

SueTons.

Regards,
SQLisAwe5oMe.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

No problem. One tweak for clarity. My query should have qualified the table with the new name of master, not master again:

SELECT *

FROM new_name_of_master.sys.configurations

WHERE name = 'max server memory (MB)';

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Was curious so just tried it myself but no dice. sys.configurations uses a rowset and a system view that eventually point back to the real master.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

If you connect to the Dedicated Admin Connection (DAC) you can run this query against the restored copy of master to retrieve the previous setting:

SELECT value AS [max server memory (MB)]

FROM new_name_of_master.sys.sysobjvalues

WHERE valclass = 50

AND objid = 1544;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 6

opc.three (3/1/2013)
No problem. One tweak for clarity. My query should have qualified the table with the new name of master, not master again:
SELECT *
FROM new_name_of_master.sys.configurations
WHERE name = 'max server memory (MB)';

Yea, I think only way is to start services and check the memory settings.

SueTons.

Regards,
SQLisAwe5oMe.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Nah, see my last post.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 8

opc.three (3/1/2013)
Nah, see my last post.

Yea got it. I replied before I read your post.

Thanks again.

SueTons.

Regards,
SQLisAwe5oMe.