About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Monday, December 19, 2011

Automatically calculate the MAX memory setting for a dedicated SQL server installation

Hello everyone,

NOTE: Updated scripts below as of March 1, 2015

I have created the following script to calculate MAX memory setting on a dedicated SQL server.
The script will show total memory, MAX memory in use, MIN memory in use, New calculated MAX and MIN memory setting.

There are 2 versions of this script because of how to detect the total memory of the SQL Server.

MAX Memory script for SQL Server 2005 to 2008R2

MAX Memory script for SQL Server 2012 and higher

Enjoy!

Rudy


Posted by at

2 comments:

  1. AnonymousMay 1, 2012 at 7:35 AM

    Great Script.. Thank You. Can you add direction on how to take your script results and have it use the MAX MEM (SQLMaxMemoryMegaByte) results to automatically change/update the SQL settings (i.e. sp_configure max server memory)?

    ReplyDelete
  2. Who am I?May 1, 2012 at 8:45 AM

    Hello Tec_Chief,

    Thank you for your comments. The code can be modified to do what you are requesting but I haven't done the automatically updating sp_configure but here is how to start.

    USE master;
    GO
    -- All sp_configure to show all options
    EXEC sp_configure 'show advanced option', '1';
    GO
    -- View setting of sp_configure
    EXEC sp_configure
    GO
    -- Set max server memory
    EXEC sp_configure 'max server memory (MB)', @SQLMaxMemoryMegaByte
    GO
    RECONFIGURE
    GO

    NOTE*** The code here does not work because sp_configure doesn't see to like variables (@SQLMaxMemoryMegaByte) so you will have to play around with this part in order to get it to work.

    Hope this helps and thanks for reading my blog.

    If you decide to modify my script and post is it somewhere, please mention my name. I'm trying to get more people to ready my blog.

    Thanks,

    Rudy

    ReplyDelete

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)