May 18, 2012 at 5:00 am
I have enabled AWE and configured the max memory min memory etc yesterday.
I gave the 'lock pages in memory' privilege to a Windows AD account. This account is used for both the SQL Server and SQL Server Agent services and also has the sysadmin role.
The overnight maintenance plans ran fine except one. This is failing at the 'Alter' stage which is doing the index rebuild of the system databases. All the other jobs run fine.
I would have expected them all to fail, but I am guessing the failing job is something to do with the privileges needed for the 'Alter Index' part.
Various errors from error logs and maintenance plans;
"Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process."
Check Database integrity on Local server connection
Databases: All system databases
Include indexes
Task start: 2012-05-18T09:50:15.
Task end: 2012-05-18T09:50:15.
Failed:(0) Alter failed for Server
The package execution returned DTSER_FAILURE (1)
Any Ideas?
May 18, 2012 at 8:24 am
thunderousity (5/18/2012)
"Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process."
Check Database integrity on Local server connection
Databases: All system databases
Include indexes
Task start: 2012-05-18T09:50:15.
Task end: 2012-05-18T09:50:15.
Failed:(0) Alter failed for Server
The package execution returned DTSER_FAILURE (1)
Any Ideas?
I think you have not restarted your SQL Server after you provided the Lock pages in Memory rights to the service account. You need to restart the SQL Server service once you provide this permission so that this setting can take effect.
May 18, 2012 at 10:04 am
I thought I would re-trace my steps again and see what is going wrong.
A little more background;
1. We have 2 instances on the server; Production and Test (Not ideal I know)
2. The 2 instances share the same service accounts for SQL Server and SQL Server agent.
I switched off AWE using SSMS on both instances, re-ran the maintenance plan and it works as usual.
Once again I configured the Test instance for AWE and restarted the Test SQL Server.
The maint. plan still works.
However, when I try to enable AWE through SSMS for the Production instance I get the same AWE and lock in memory error I got from the failing maint. plan. (I may have ignored this error last time as when you go back into SSMS > Properties > Memory, it is showing AWE is enabled???)
However in sp_configure for the Production instance it is showing config_value 1 run_value 0
I haven't yet restarted Production as I wanted to ensure everything is ok first.
The maint. plan on the Test instance still works after the attempt at enabling AWE on the Production instance
Could it be something related to the instances sharing the same Windows AD account for SQL Server and the Agent. i.e. they both need a reboot for anything to take effect?
May 22, 2012 at 9:53 am
It sounds like you did not do all the steps or you may not have a current enough build. What does this return on your system:
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
(
SELECT CEILING(CAST(physical_memory_in_bytes / (1024.0 * 1024.0) AS DECIMAL(12, 2)))
FROM sys.dm_os_sys_info
) AS [Physical Memory_MB],
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS [min server memory (GB)],
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS [max server memory (GB)],
(
SELECT CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2))
FROM sys.dm_os_sys_info
) AS VAS_GB,
(
SELECT value_in_use
FROM sys.configurations
WHERE name = 'awe enabled'
) AS [awe enabled];
Using LPIM in x86 SQL 2005 Standard (please post results of query above) requires at least Service Pack 3 + cumulative update package 4 or higher.
Once you are on a current enough build:
For x86 SQL 2005 Standard Edition these are the steps to follow:
- grant LPIM privilege
- add trace flag 845 to startup parameters
- enable AWE using sp_configure
- restart server
Verify that the following message is written in the SQL Server error log file at startup:
Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
For x86 SQL 2005 Enterprise/Developer/Evaluation Edition:
- grant LPIM privilege
- adding trace flag 845 is not necessary and is ignored
- enable AWE using sp_configure
- restart server
Verify that the following message is written in the SQL Server error log file at startup:
Using locked pages for buffer pool.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2012 at 8:17 am
Edition: Standard Edition
Product Version: 9.00.4309.00
Product Level: SP3
Physical Memory_MB: 8184
min server memory (GB): 2.00
max server memory (GB) 4.00
VAS_GB: 2.00
awe enabled: 0
Looks like I am at a sufficient version SQL Server 2005 Standard Edition (SP3) >= 9.00.4226
So do I need to 'add trace flag 845 to startup parameters'. Is this to prevent SQL Server slowdowns/shutdowns when the OS is running low on memory? I thought stipulating a min and max server memory would prevent this.
May 25, 2012 at 9:30 am
thunderousity (5/25/2012)
Edition: Standard EditionProduct Version: 9.00.4309.00
Product Level: SP3
Physical Memory_MB: 8184
min server memory (GB): 2.00
max server memory (GB) 4.00
VAS_GB: 2.00
awe enabled: 0
Looks like I am at a sufficient version SQL Server 2005 Standard Edition (SP3) >= 9.00.4226
So do I need to 'add trace flag 845 to startup parameters'.
Yes.
Is this to prevent SQL Server slowdowns/shutdowns when the OS is running low on memory?
The trace flag allows SQL Server Standard Edition to lock pages in memory.
I thought stipulating a min and max server memory would prevent this.
In a perfect world that would be the case. However LPIM is sometimes necessary to elevate SQL Server's ability to hold onto memory and prevent Windows from compelling it to release it in large chunks, often at inopportune times.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply