August 19, 2003 at 2:45 pm
Hi All,
We are running SQL Server 2000 SP3 on Windows 2000 SP2. As part of the setup, SQL Agent is paging as soon as a job fails. Today we realized that most of the jobs are NOT running for the last 5 days on this box. But I could see 3 jobs running successfully daily & hourly as well. I have no idea why only 3 jobs are running while the rest of them are showing status as "performing completion actions". I tried to start one of the jobs this morning again, but it wouldn't respond.
Can anyone help me understand what this problem is? Also how to rectify in future?! Is checking all the jobs manually daily the only option?!
PLEASE HELP...
Regards,
MD.
.
August 19, 2003 at 2:47 pm
Since this very time critical, I have rebooted the Server. Now everything seems to be working. But I still didn't understand what went wrong.
Can someone help.....
.
August 19, 2003 at 4:04 pm
I've seen this a few times and never figured it out. Only a reboot has solved it.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 19, 2003 at 4:08 pm
But how do we check if this is HUNG or Running with FAILURE/SUCCESS?
Do we need to check few times in a day whether the jobs are running not not?!
Does anyone has any scripts & strategy to check this periodically?
I am running SQL 2K SP3 on Windows 2000 Server SP2. Will Win2K SP3 or SP4 help my situation?!
.
August 20, 2003 at 10:42 am
This may seem like a strange question, but is there an Outlook client installed on the SQL Server?
August 20, 2003 at 10:44 am
Yes. Outlook 2000 is installed on the box as SQL Server 2000 running.
.
August 20, 2003 at 12:34 pm
We have found that removing the Contacts Address Book from Outlook keeps this problem from happening. (Properties of Outlook, properties of Outlook Address Book, highlight Contacts, Remove). I know this doesn't seem logical, but it has helped us so much that we have even added this step to our standard install document.
Give it a try!
August 22, 2003 at 7:13 am
Thanks to Steve & annejo for your inputs.
.
September 9, 2003 at 7:17 am
I just posted another EMail problem on this site but have had nothing but problems with EMail and SQL Server - Even using all Microsoft products. I don't recall the link but be VERY careful of the SendMail command - especially using it with embedded queries. The Sendmail command will LOCK your system waiting for a query reply and may hold locks on tables as well. THis may explain why some jobs worked and others did not. Regardless - check for ALL USE of xp_SendMail and remove them if they use queries as output.
Search on the MS site for Sendmail and Locking and you'll see many, many bugs, workarounds, etc..
Kinda scary...
September 9, 2003 at 8:10 am
quote:
Regardless - check for ALL USE of xp_SendMail and remove them if they use queries as output.Search on the MS site for Sendmail and Locking and you'll see many, many bugs, workarounds, etc..
Kinda scary...
I really ought to get round to writing an article on the pitfalls of SQLMail...
From Books Online, Transact-SQL Reference for xp_sendmail:
quote:
One SQLMail session supports all users on the SQL Server, but only one user at a time can send a message. Other users sending mail messages will automatically wait their turns until the first user's message is sent.
So if you're using SQLMail regularly, you may experience slow-running, locks etc, while other SQLMail queries are processed first.
quote:
Note: query can be blocked by a lock held by the client connection issued xp_sendmail. For example, if you are updating a table within a transaction and you create a trigger for update that attempts to select the same updated row information as the query parameter, the SQL Mail connection is blocked by the exclusive lock held on row by the initial client connection.
This is one thing that we suspect may be causing problems for us. We have various jobs that try to send notification emails, and occasionally these fail / lock each other up. This will be exacerbated if you've got "Notify operator by email" selected in the schedule!
I've been hoping to get some time to go through all our code looking for use of xp_sendmail, and replace it with use of a table and some VB code to handle this. It should also give us more control over the appearance of the message! All I need is the time!
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 9, 2003 at 8:22 am
List of problems with xp_Sendmail:
http://search.microsoft.com/search/results.aspx?View=en-us&p=1&s=0&c=3&st=a&qu=xp_sendmail&na=30
My problem with a query and xp_Sendmail:
http://support.microsoft.com/default.aspx?scid=kb;en-us;164998
A Summary...
"Another limitation of SQLMail is that if xp_sendmail is called with the optional @query parameter, SQLMail makes a loopback connection that blocks the KILL command. For example, suppose a client with process id (spid) 10 runs xp_sendmail with a long running query, which is on spid 11. If the Kill command is used on spid 10, the SQL Server errorlog reports that the spid was killed. However, the spid keeps running because it is waiting on the results of spid 11. In this situation, the Kill command must also be run on spid 11 to terminate the client. Unfortunately, there is no way to determine the loopback spid used by SQLMail."
Once SQL Server gets in this mode - you're totally hosed. Even the KILL command does not work. Be CAREFUL with sendmail and embedded queries !!
- B
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply