Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Max server memory settings Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 2:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 PM
Points: 422, Visits: 2,661
How can I get a max server memory setting of SQL if it's not running/disalbled?

SueTons.


Regards,
SQLisAwe5oMe.
Post #1425743
Posted Friday, March 1, 2013 3:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425767
Posted Friday, March 1, 2013 3:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 PM
Points: 422, Visits: 2,661
Thank you.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1425774
Posted Friday, March 1, 2013 3:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425776
Posted Friday, March 1, 2013 5:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425794
Posted Friday, March 1, 2013 5:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
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
Post #1425795
Posted Friday, March 1, 2013 5:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 PM
Points: 422, Visits: 2,661
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.
Post #1425796
Posted Friday, March 1, 2013 5:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 7,135, Visits: 12,744
Nah, see my last post.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1425798
Posted Friday, March 1, 2013 6:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:40 PM
Points: 422, Visits: 2,661
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.
Post #1425799
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse