Home > SQL > SQL 2005 SQL Server Agent (Disabled) and won’t start

SQL 2005 SQL Server Agent (Disabled) and won’t start

Ok, I thought I would post about this as it caught me by surprise today, We have a SQL 2005 Std server that I use to host our main databases and our Data Warehouse.

It has all be running fine for a while, and today my MD commented that our reports show last Thursdays figures as incomplete, which I considered impossible as our Data Warehouse does a full load every morning and if there were figures for Friday then Thursday must be in there as well, well in my world!!

But when looking at the server I noticed that the SQL Agent was disabled and I could not start it, nore could I see the jobs on the server. I spent a few hrs and search the web for a while trying to figure out why the server now has the agent as disabled.

I found a error in the SQLagent,out found in the logging folder in my case C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\

2008-02-08 13:40:26 – ! [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)
2008-02-08 13:40:26 – ! [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)
2008-02-08 13:40:26 – ! [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]
2008-02-08 13:40:26 – ? [100] Microsoft SQLServerAgent version 9.00.3042.00 ((Unknown) unicode retail build) : Process ID 5552
2008-02-08 13:40:26 – ? [101] SQL Server  version 9.00.3054 (0 connection limit)
2008-02-08 13:40:26 – ? [102] SQL Server ODBC driver version 9.00.3042
2008-02-08 13:40:26 – ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is SQLSRV\SQL
2008-02-08 13:40:26 – ? [310] 4 processor(s) and 3071 MB RAM detected
2008-02-08 13:40:26 – ? [339] Local computer is SQLSRV running Windows NT 5.2 (3790) Service Pack 2
2008-02-08 13:40:26 – ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ‘(Unknown)’ is not a member of the SysAdmin role
2008-02-08 13:40:26 – ? [098] SQLServerAgent terminated (normally)

The problem seems to be the first line, I do not know what happened, if it was a update or something else, updates were applied last week and a reboot was preformed.

to fix follow these steps

 

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

    The Group Policy dialog box opens.

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

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

  4. Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

  5. In the pane, double-click Lock pages in memory.

  6. In the Local Security Policy Setting dialog box, click Add.

  7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

I added the local admins group and SQL server service account.

 

Hope this helps

Advertisements
Categories: SQL
  1. February 25, 2012 at 6:02 am

    Nice work

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: