check the status of SQL Agent

Hi all

in my project, I need to access SQL job to finish something. But sometimes, the status of SQL Agent is not running, which needs me to check the status first. I am wonder are there some functions or some ways to check the status. If you know, please response me.

I appreciate your response !

You don't need to check for status if you create a proxy account for the Agent, that is clone an Admin level permissions to run the Agent, run a search for SQL Server Agent Proxy account in the BOL(books online). The reason is when you run a Job manually it runs in the context of your permission as a Job it becomes the Agent's which means it needs your level of permissions to run it. Hope this helps.|||

Hi Caddre

Thanks for your response. I project workd is like this:

it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?

Thank you very much !

|||

Liu_andi:

Hi Caddre

Thanks for your response. I project workd is like this:

it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?

Thank you very much !

When you start SQL Server you are starting the SQL Server service it is different from the Agent without the proxy account the Agent will not be running in an application as you expect it, threads are not relevant to the issue it is related to the Agent having enough permission to run, it takes a lot of people time to understand but fix it or your jobs will not run as you expect. Hope this helps.

|||

Hi Caddre

you are so nice to give me response.

you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:

exec master..xpcmdshell 'net start'.

the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?

|||

Other way I think is more better to use:

declare @.service nvarchar(100)

select @.service = case when charindex('\',@.@.servername)>0
thenN'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end

exec master..xp_servicecontrol N'QUERYSTATE', @.service

the return is "Running.", which expresses the status of SqlAge is running.

|||

Liu_andi:

Hi Caddre

you are so nice to give me response.

you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:

exec master..xpcmdshell 'net start'.

the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?

This is a query against one database that exposes all your SQL Server that is not prudent, what I am telling you to do is a simple process running under a service clean and valid. I am telling you to do it the correct way what you actually do is your employer's business. Hope this helps.