November 27, 2009 at 5:41 am
I have a scheduled job on analytics server that pulls a report from analytics db, save it in a shared path and then send the same as a mail attachment to few persons.
Initially, it was scheduled at 7:45am daily and running fine. But, suddenly on a given day we found it running for hours and the all other schedules that followed it.
We rescheduled it. Created a new report(having same columns) and new schedule. Changed the setting as 'save file only'. Or, as 'Send mail only'. Nothing helped.
But, the strange thing is that the schedule run fines after 5pm, which is not a off-peak hour. Peak hour is up to 7pm.
Investigations so far: sql profiler shows it is executing the sproc for saving report to excel. Although, the excel report is getting saved to the shared path immediately after the schedule begins. (no issue with report).
Report size isn't an issue. that is of only 3mb. Few schedules are transporting bigger report than this.
What I do: Stop the job. Kill the tasks tlrptxlu.exe and excel.exe on analytics box. tlrptxlu.exe is my custom application and it appears to be hanged in such situation. These two tasks should end automatically when i stop that job from SSMS. But, it keeps running without any decay in memory consumption. This futher prevents the next schedules from using the excel and saving the report to the shared path\(or temp path to be used as an attachment for the mails waiting in outbox).
Remember, this runs fine after 5pm and its very puzzling what prevents the same from going fine earlier.
Expecting your valuable suggestions to identify the cause and fix it.
November 27, 2009 at 6:36 am
Did you check for any open transactions? Was there any blocking?
-Roy
November 27, 2009 at 9:56 pm
Yes Roy. I checked that too. The SPID (of the sick job) is neither blocked by any other spid, nor it blocks any. I am not considering stop job scripting at this point of time.
November 28, 2009 at 3:49 pm
What are you using to send the Email? I ask because this looks very much like a series of known problems with SQL Mail, that are generally fixed in DBMail.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2009 at 4:19 pm
You should try to enable a SQL Job output Log to some log file and verify what was the last step done and get more info about this.
December 2, 2009 at 12:53 am
Dear Sirs,
This is a CRM application and sending mail is part of the application. we are not using sql mail or db mail. anyway I have escalated the issue to the product management team to check as could not find any sql related issue with this. there are some DLLs that of the application/ product that work with SQL server. I wil wait for their feedback before I furthur dig into sql server.
Thanks a lot for your valuable suggestions.
December 3, 2009 at 11:50 am
sandeep singh-337370 (12/2/2009)
Dear Sirs,This is a CRM application and sending mail is part of the application. we are not using sql mail or db mail.
Then this almost certainly a problem with your application and not SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply