SQL Max server memory settings

  • How can I get a max server memory setting of SQL if it's not running/disalbled?

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • 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

  • Thank you.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • 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

  • 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

  • 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

  • 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.

  • Nah, see my last post.

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

  • 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.

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

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