SQL Server Agent is unable to come online on a cluster
Going through Varun's blog on Lock Pages in Memory in SQL Server 64-Bit, I remembered an issue that I had seen very recently. Thought of putting this down in my blog, to help anybody facing a similar issue. This issue happens typically on 32 bit environments; although I have not tested the same on a 64bit environment.
One of my customers, in this case, had an instance of SQL Server Enterprise Edition 2005 on a Windows 2003 Cluster. One of the nodes had some issues, because of which, they had to rebuild it. After rebuilding the node, they added it back to the Windows Cluster and then add the node to the SQL Server Cluster as well. However, when they tried to failover the SQL Server resources to the new node, all resources would come online on this new node, and after a few seconds, the SQL Server Agent would fail, and all the resources would move back to the previous node.
I was called for help, and my first reaction was to check the SQL Server Errorlogs and the SQL Server Agent Logs. Well, the SQL Server Errorlogs did not reveal nuch information. However, the SQL Server Agent Logs revealed the mystery. The SQL Server Agent Logs had the following information:
2009-03-10 00:48:21 - ! [298] SQLServer Error: 5845, Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process. [SQLSTATE 42000] (DisableAgentXPs)
2009-03-10 00:48:21 - ! [298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2009-03-10 00:48:21 - ! [298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000]
...
2009-03-10 00:48:21 - ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role
2009-03-10 00:48:22 - ? [098] SQLServerAgent terminated (normally)
Quite self-explanatory, isn't it! The DBA had not granted the SQL Server service account the priviledge to "Lock pages in memory". Since this right had not been granted, AWE could not be enabled when SQL Server was running on the newly added node. Since AWE could not be enabled, Agent XP could not be turned on; since Agent XP could not be turned on, SQL Server Agent failed to come online.
All I did was to assign the SQL Server service account the right to "Lock pages in memory"; and the issue got fixed!
I did some research in my test servers. On a standalone instance of SQL Server 2005 Enterprise Edition, if the SQL Server service account does not have the priviledge to "Lock pages in memory", AWE cannot be enabled. If we try to enable AWE, we get the following error message:
Msg 5845, Level 16, State 1, Line 2
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.
However, Agent XP can still be enabled, and the SQL Server Agent can still come online. Here is an extract from the sp_configure output:
name minimum maximum config_value run_value
--------------- ------- ------- ------------ ---------
Agent XPs 0 1 1 1
awe enabled 0 1 1 0
On a clustered instance of SQL Server, on the other hand, in similar conditions, Agent XP cannot be turned on and SQL Server Agent would fail to come online. If we look at the sp_configure output at this time, we would find the following:
name minimum maximum config_value run_value
--------------- ------- ------- ------------ ---------
Agent XPs 0 1 1 0
awe enabled 0 1 1 0
Recommended solution: Provide the SQL Server service account the right to "Lock pages in memory".
Additional Reading: How to: Enable the Lock Pages in Memory Option (Windows)
Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.
Comments
Anonymous
March 14, 2009
PingBack from http://www.clickandsolve.com/?p=23239Anonymous
July 06, 2010
Thanks, well explained This actually helped me resolve one of the issue.Anonymous
January 14, 2012
Great post help me immensely and saved me alot of troubleshooting time.Anonymous
December 02, 2012
Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course www.wiziq.com/.../125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance... would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.