You are currently offline, waiting for your internet to reconnect

SQL Server only uses 2 GB of memory even though the AWE option is enabled

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SQL Server only uses 2 gigabytes (GB) of memory even though SQL Server is configured to use the Microsoft Windows 2000 Address Windowing Extensions (AWE) API to support a maximum of 64 GB of physical memory when the awe enabled option is set to 1. Windows Task Manager also shows that SQL Server is using only 2 GB of memory.

In the SQL Server log, you will see the following message:

Cannot use Address Windowing Extensions because lock memory privilege was not granted.
The user account the SQL Server service is running on was not added to the Lock Page in Memory policy of the User Rights Assignments in the Local Security Policy of the computer.
To resolve this problem, follow these steps:

Note: You can only perform these tasks at the individual server console. To perform these tasks, you must log on to Windows as a local administrator on the computer where SQL Server exists.

  1. On the Taskbar, click Start, and then click Programs.
  2. Click Administrative Tools, and then select Local Security Policy.
  3. Expand Security Settings, expand Local Policies, and then click User Rights Assignments.
  4. In the right screen, right-click Lock pages in memory, and then click Security.
  5. In the Local Security Policy Setting dialog box, click Add.
  6. Click to select the account where the MSSQLSERVER service is running.
  7. Click OK.
  8. For Microsoft Windows 2000 Server, in the command window, type Secedit /refreshpolicy machine_policy /enforce. For Microsoft Windows Server 2003, in the command window, type GPUPDATE /force.
  9. Restart the SQL Server service.
If your instance of SQL Server is installed on a computer that is running Windows 2000 Advanced Server, Windows 2000 Datacenter Edition, Windows Server 2003, or Windows NT, you can use the /3GB switch in the Boot.ini file on your computer. As a result, SQL Server 2000 can use up to 3 GB of available memory on your computer.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274750 How to configure SQL Server to use more than 2 GB of physical memory
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
329914 AWE-enabled SQL Server 2000 may take a long time to start
298444 A description of the Group Policy Update utility

Article ID: 811891 - Last Review: 04/12/2006 15:52:42 - Revision: 4.2

Microsoft SQL Server 2000 Enterprise Edition, Microsoft Windows 2000 Advanced Server, Microsoft SQL Server 2005 Enterprise Edition

  • kbsysadmin kbsecurity kbprb KB811891