AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1884&pId=-1
SQL Server Agent - Agent XPs Disabled
page
by Aamod Thakur
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30089/ 68

Introduction

Today, Disk space on one of my company's server was Full. As a Massive database was growing more massively, we were moving some databases to another drive. At one point the SQL Server got stuck.After rebooting the server, noticed that SQL Server Agent was disabled.  Message shown in the Object Explorer was SQL Server Agent (Agent XPs disabled). Well, I had seen this message a few times on other Servers before this but had ignored it as there werent any important Maintainence Plans or Jobs Scheduled. But this time that Server was loaded with important Jobs and Maintainence plans hence had to find out a Solution this time .

What is Agent XPs option?

Agent XPs option is used to enable the SQL Server Agent extended stored procedures on the server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.

Note: SQL Server Management Studio Object Explorer does not display the SQL Server Agent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state. i.e Even if the SQL Server Agent Service is Started or Stopped ,you will still see the  SQL Server Agent (Agent XPs disabled) in object explorer.

How do we get out of this SQL Server Agent (Agent XPs disabled) problem?

Just run the below code.

sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'Agent XPs', 1;
 GO
 RECONFIGURE
 GO

Once this is done, Refresh your Object Explorer or SQL Server Agent node and its all done.

SQL Server Agent node is enabled for you.

Conclusion

For more details you can refer to the MSDN Article: http://msdn.microsoft.com/en-us/library/ms178127(SQL.90).aspx

Hope This Helps. Please do reply if this post works for you so that others can make use of the same.



©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-22 5:03:34 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search