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 01, 2013 2:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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 01, 2013 3:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425767
Posted Friday, March 01, 2013 3:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
Thank you.

SueTons.


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425776
Posted Friday, March 01, 2013 5:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425794
Posted Friday, March 01, 2013 5:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425795
Posted Friday, March 01, 2013 5:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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 01, 2013 5:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
Nah, see my last post.

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1425798
Posted Friday, March 01, 2013 6:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:04 PM
Points: 383, Visits: 2,351
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