Aug
19
2011

SQL Server agent not starting

Today faced an issue that SQL server agent was not starting up, after investigation found that issue is due to AWE and lock pages in memory settings , found a very useful article at following link

It says,

 I changed my SQL Server Agent service account to a domain account using SQL Server Configuration Manager (should always use this tool to manipulate service accounts), but then could not start the SQL Server Agent service. This was the fifth box I’d made this change and did not see any problems on the previous four. A couple of errors:

——————–

SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ‘(Unknown)’ is not a member of the SysAdmin role).

——————–

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.

——————–

After some Googling, the top 5-6 posts said to enable the use of ‘Agent XPs’ using the sp_configure stored procedure. Made sense, as the error told me to do so also. But, when trying to enable Agent XPs, I would get “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” Those top 5-6 posts also said to change Windows Group Policy to allow the account running the SQL Server Agent service to ‘lock pages in memory’.

Whoa! I went to my other servers where the SQL Server Agent service is running successfully and checked their Group Policy, none of the domain accounts I was using on those machines needed to have ‘lock pages in memory’ enabled. This raised a flag, as I do not want my servers configured differently.

I found that on my problematic server, the ‘awe enabled’ option in the sp_configure list had a ‘config_value’ of 1. The ‘run_value’ was still 0. Changing the ‘config_value’ back to 0 solved my problem. I only have 2gbs of memory on the box. SQL Server Books Online discusses the differences between the two values, as well as what the ‘awe enabled’ option does for the instance

Long winded, but hopefully helps others. Here’s a list of various commands I ran during my troubleshooting:

— by itself, shows options that are set
sp_configure

— to enable all options to be shown
sp_configure ‘show advanced options’, 1;
reconfigure
go

— to change value of Agent XPs
sp_configure ‘Agent XPs’, 1;
reconfigure
go

— to change value of ‘awe enabled’
sp_configure ‘awe enabled’, 1;
reconfigure
go

To view/change Windows Group Policy:

1. On the Start menu, click Run; in the Open box, type gpedit.msc.

2. The Group Policy dialog box opens.

3. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

4. Expand Security Settings, and then expand Local Policies.

5. Select the User Rights Assignment folder.

The policies will be displayed in the details pane.


Other useful links I found are:

How to enable Agent XP’s – link

How to enable lock pages in memory option – link

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: