How do you troubleshoot SQL Server Agent in SQL 2005?

Question

How do you troubleshoot SQL Server Agent in SQL 2005?

webrunner

SSC-Dedicated

Points: 31862
More actions
September 12, 2007 at 8:07 am

#176583
Hello,
I know there is a lot of information on SQL Server Agent out there, but I have not been able to narrow it down to help me resolve a problem.
The symptoms are:
1. Some Agent jobs are running, but some aren't.
2. Recreating jobs has worked in some cases but not others.
3. No job history is being written for any jobs.
4. We see this error intermittently, apparently coinciding with when jobs are scheduled to run: [382] Logon to server '[servername]' failed (ConnAttemptCachableOp)
5. We have a job to delete old backup files, which uses master.dbo.xp_delete_file, and that is one of the jobs that is not working (no files are being deleted when it runs) - is this procedure affected by any SQL Server Agent problem we might be having?
Can anyone recommend a process to diagnose this problem and - if possible - a way to resolve it, preferably without needing to restart the SQL Server?
I think I have found the approximate time that the problem started - if someone knows of any log entries or errors I can look for around that time to shed light on the problem, it would be a great help.
Also, we are running an active/passive cluster, in case that info helps.
Thanks for any help!
webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Does bouncing the SQL Server Agent service help at all?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

webrunner SSC-Dedicated Points: 31862 More actions · Answer 2

Hi Matt,

Thanks for your reply. Unfortunately, in this case, bouncing the SQL Server Agent service did not solve the problem. It appears to have helped some of the jobs to start running again (a couple of the backup jobs starting producing files again), but not all of them, and it did not fix the issue where jobs are not being written to the job history.

We have a mix of jobs - some are maintenance plans made through the Maintenance Plan Wizard, while others are jobs without maintenance plans that run a stored procedure on a schedule. So far I don't see a pattern such as 'only the maintenance plan jobs are failing' or anything like that, but if you can suggest ways of checking on that, please pass the info along.

FYI, we restarted SQL Server Agent from the Cluster Administrator using the right-click Take Offline.../Bring Online... commands.

I have already put in for getting the SQL Server service (and the whole server if needed) restarted during the next available maintenance window, but I would like to see if I can find the cause and learn why it happened (and how to fix it) before we do a more broad restart, because although I hope that would cause the problem to go away it might also wipe out any way of finding the true cause.

Thanks again for any help you can give.

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31862 More actions · Answer 3

PS - Is SQL Server Integration Services needed for SQL Server Agent to work properly?

I thought that was the case, but I am not sure if that applies only to jobs that require SSIS packages for maintenance plans, or to the agent in general.

The reason I ask is that I don't see SQL Server Integration Services in the list of services when I open SQL Server Configuration Manager.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

What level is your SQL2005 (sp2 with CU3 ?)

- check the job-owner for the jobs.

- if you want to run dts-packages, you'll need ssis

SSIS needs to be installed separatly on both nodes of your cluster !

You need to make it "cluster aware" yourself.

(http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx)

- Keep in mind, security is one of the things that have been improved with SQLAgent 2005, so it may need e.g. proxies, ...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

webrunner SSC-Dedicated Points: 31862 More actions · Answer 5

Sorry for the delayed reply.

We are running SQL Server 2005 SP1.

I don't think the issue is limited to DTS packages, since job history is not being logged for any jobs, not even a single-step job where the single step is an update statement. Are there any errors that might signal an SSIS or DTS problem?

Also, we tried restarting not only the SQL Server Agent but the whole SQL Server (first we tried restarting the SQL Server service, then we rebooted the server), and still no history is being logged for jobs.

Regarding security, where can I learn more about whether security/proxies might be an issue?

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

The answer is very simple depending on what the job is doing the Agent needs admin permissions or proxy admin permissions because deleting backup files is very serious business Microsoft just want their customers to know who permitted such task if needed. The SSIS packages is also another place the Agent needs admin permissions because we web developers use those for cross platform data automation which Microsoft also thinks their customers need to know who approved it. Hope this helps.

Kind regards,
Gift Peddie