sql jobs started hangin

[Luvsql]

I have many sql jobs setup to run and email results. Whenever we have any sort of email issues, my jobs start hanging. I have stopped and started sql agent multiple times, but when I try and start the jobs again, they hang. The jobs are doing simple select statements and those run successfully on their own.

Is there an easy way to find where this is hanging? Only these jobs are failing (ones with sendmail commands). I can select the operator and send a test email successfully.

 

[mrdenny]

SQL Agent and SQL Mail are not connected. You need to restart the SQL Mail component of SQL Server is order to get it to reconnect to the mail server.

It's a good plan that any time the mail servers go down to restart SQL Mail.

exec xp_stopmail
go
exec xp_startmail
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luvsql]

I'm not able to stop sql mail:

Server: Msg 17955, Level 16, State 1, Line 0
xp_stopmail: Failed to stop SQL Mail session.

 

[mrdenny]

What happens why you try to start it?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luvsql]

It says that it's already started:

SQL Mail session is already started.

 

[mrdenny]

Sounds like something major is wrong with SQL Mail. Try restarting the SQL Service. That will forse SQL Mail to stop.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com