how to configure sql server memory

  • I tried to google the querys I need to run inside query analyser in order to configure sql server memory to use 3GB, but I could'nt find. Please advice and also let me know the change I should do to the bin\config.sys file. Thanks!

  • Use:

    EXEC sp_configure 'show advanced options', '1'

    RECONFIGURE WITH OVERRIDE

    EXEC sp_configure 'min server memory', '0'

    EXEC sp_configure 'max server memory', '3000'

    RECONFIGURE WITH OVERRIDE

    EXEC sp_configure 'show advanced options', '0'

    RECONFIGURE WITH OVERRIDE

    HTH.

    MJ

  • Do I need to change config.sys file under the bin folder? Thanks.

  • You've left a lot of important information out.

    32 bit? 64 bit?

    What OS

    What edition of SQL 2005?

    How much memory does the server have?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/30/2008)


    You've left a lot of important information out.

    you may or may not need to configure the /3GB switch in the boot.ini (i assume you meant this file and not the config.sys) and also enable AWE

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • In addition to running the below queries:

    Do we also need to configure the /3GB switch in the boot.ini file?

    When do we enable AWE? before running these queries?

    EXEC sp_configure 'show advanced options', '1'

    RECONFIGURE WITH OVERRIDE

    EXEC sp_configure 'min server memory', '0'

    EXEC sp_configure 'max server memory', '3000'

    RECONFIGURE WITH OVERRIDE

    EXEC sp_configure 'show advanced options', '0'

    RECONFIGURE WITH OVERRIDE

    Thanks!

  • Mh (11/30/2008)


    In addition to running the below queries:

    Do we also need to configure the /3GB switch in the boot.ini file?

    When do we enable AWE? before running these queries?

    No idea. You haven't given us enough information. Please answer the questions I asked.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello !

    I am kind of looking for the same answers.

    Do I have to enable AWE and switch \3GB on 64 BIT SERVER 8GB RAM , Windows 2003 Standard /SQL 2005 Standard?

    So far I configured max memory to 6GB on server

    with minimum 1 GB.

    Thank you.

  • No. 3gb and AWE are for 32 bit only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • THANK YOU.

    I guess this is not going to be problem to reverse it if I already enabled AWE in sp_configure

  • I have a question about memory. We added 32 gig on the server. OS = Server 2003, SQL Server 2005 Standard

    SQL and the application that is using it is on the same server. Getting alot of timeout errors in the application that seem to point back to SQL.

    Not sure where to go for help.

  • what errors particularly. Can you post some samples

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Time: 11/10/2009 8:42:02 AM

    Type: Error

    Source: cSupport Desktop

    Event ID: 0

    Message:

    Database Transaction Rollback Occurred.

    *********************************************

    The rollback occurred due to the following exception:

    System.Data.SqlClient.SqlException

    Message:

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    The statement has been terminated.

    Stack Trace:

    *********************************************

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    at Gwi.Data.Access.DataAccessService.Execute(CommandType statementType, String sql, Int32 commandTimeout)

    at Gwi.Data.Access.DataAccessService.Execute(CommandType statementType, String sql)

    at Gwi.cSupport.Providers.Generated.IncidentProviderBase.Update(Incident incident)

    at Gwi.cSupport.Services.IncidentService.Save(Incident incident, String auditPrefix)

    Connections to the following databases were rolled back:

    Database: cSupport

    Additional Information

    *********************************************

    TimeStamp: 11/10/2009 8:42:02 AM

    OS Platform: Win32NT

    OS Version: 5.2.3790.131072

    CLR Version: 2.0.50727.3603

    MachineName: CGVL-CSUPPORT

    WindowsIdentity: CGVL-CSUPPORT\c.Support

    Support Rep Identifier: 44

    Requested URL: http://cgvl-csupport/Rep/incident/default.aspx?ID=28901

  • verify connectivity to the sql server and database. from a remote machine start a sqlcmd session and connect then query the database. What results do you get?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 14 posts - 1 through 13 (of 13 total)

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