Post reply

agent stopped and we dont know abt it

  • November 27, 2005 at 2:32 pm

    #111530

    Hi friends

    we have sql server 2000 on a server machine.and we've some maintenance jobs that run at 6.30pm everyday. the last week our system administrator has changed administrator password and suddenly Sql agent service stopped saying "logon" failure!!

    we only came to know today that service is actually stopped and it has not been running any of maintenance jobs!!

     
    is there any way that when service stopped for any reason or when job failed to get any notification. BTW In my abv case we dont have any errors in the log as agent service is not running at all.
     
    yah , we changed password for this service(under services) and got it working again.

    Thanks for your help.

  • November 27, 2005 at 6:01 pm

    #606004

    You can use the extended sp master.dbo.xp_servicecontrol

     

    create table #SQLServiceStatus

    (status nchar(30))

    insert into #SQLServiceStatus

    EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent'

    --for 'SQLServerAgent'

    insert into #SQLServiceStatus

    EXEC master.dbo.xp_servicecontrol 'QueryState', 'MSSQLServer'

    --for 'MSSQLServer'

    It returns 'running' if running, you can use the status feild along with @@servername to insert into a log table if a service is not running, if an insert occurs you can then generate an email, and mark a bit feild to indicate that the email has been sent, either by using xp_sendmail which to be honest is a little flakey because it relies on mapi, or you could use smtp mail sp as developed by http://www.sqldev.net/xp/xpsmtp.htm. Either way if a service is not running and the server is up you can be alerted.

  • November 27, 2005 at 6:24 pm

    #606005

    Thank you very much dude.will give it a try

  • November 29, 2005 at 4:31 am

    #606371

    The only problem with doing this from SQL Server is that if the SQL Server service is the service that is not running then how is the email ever going to get sent?  Or, if you're using SQLMail then the SQLAgent service needs to be running.

    Ideally, you should monitor the services from outside of SQL Server to avoid this kind of problem.

    We remotely monitor our SQL Server services.  Check out this article:

    http://www.databasejournal.com/features/mssql/article.php/10894_3347241_2

    Karl
    source control for SQL Server

  • November 29, 2005 at 8:25 am

    #606428

    Karl,

    Thats why i mentioned smtp mail, I run the checks from a non production server and use this sp to send the mails. Either soulution, the heartbeat check or a stand alone server will meet requirements

  • November 29, 2005 at 1:04 pm

    #606524

    Thank you Karl and Andrew for your both suggestions.
     

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

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