Post reply

SQL Server agent xps is disabled.

  • June 21, 2010 at 2:42 pm

    #147686

    Hello all,

    I have a severe issue this morning.

    Actually,

    I have a copy job running on my server which is copying huge amount of data,

    During this process the clustering admin did a failover due to some emergency.

    After the failover my node N1is back and ON.

    But the database on which I am runnning the copy job is under recovering state and the sql server agent is showing SERVER AGENT XPS IS DISABLED.

    I tried to google regarding this and tried to turn on the server agent using the following code.

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'Agent XPs', 1;

    GO

    RECONFIGURE

    GO

    Now, My server agent is ON, but when I try to start or schedule a job

    It gives me the following error

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Start failed for Job 'XXX'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot perform this operation while SQLServerAgent is starting. Try again later. (Microsoft SQL Server, Error: 14258)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=14258&LinkId=20476

    ------------------------------

    I didnt understand about this error.

    And my database is still in recovery mode(only the database on which the copy job is being performed)

    Remaining databases are online

    I checked into the sql error log and it says my my database is recovering and shows me the recovering percentage like 1%.. 2%...... so on.... 10% right now.

    Can any one help me on this issue please.

    Its really urgent.

  • June 21, 2010 at 2:43 pm

    #1182988

    1

  • June 21, 2010 at 3:35 pm

    #1183005

    if sql is restarted all database have to go through a recovery phase. Your database is currently doing this as a large load was forcibly terminated and there will be a large amount of roll back. Agent XPS will not be enabled until the recovery phase is complete, SQLAgent will come online at that point.

    You will see the message 'recovery is complete' in your errorlog.

    ---------------------------------------------------------------------

  • June 21, 2010 at 3:45 pm

    #1183008

    So my sql agent will not be enabled till all the databases are recovered

    which means none of my job will be running till all DB's are recovered?

  • June 21, 2010 at 4:11 pm

    #1183017

    correct

    ---------------------------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply