June 10, 2009 at 9:29 pm
I did post this question last month , didnt get any response. I have always learned from this forum, hopefully someone does this time.I will try to explain in my best way. There is this one job which doesnt kicks off once in a while, like it stays on "Executing " state. There is no blocking nothing. No errors. There is no particular pattern in which this job fails. I have a strong feeling this has to do with msdb because the job starts but doesnt even reach to the point where it runs a SSIS package. Mentioned below is the result from sp_who2 when the job hangs. I got only the processes hitting msdb. For each process i am also writting the o/p from dbcc inputbuffer
55 sleeping AD-ENT\XYZ WPWsp4121 .msdbAWAITING COMMAND0006/09 18:00:00SQLAgent - Job Manager 55 0
dbcc inputbuffer(55)
Language Event0EXECUTE master.dbo.sp_PostAgentInfo 65536
54 sleeping AD-ENT\XYZWPWsp4121 .msdbAWAITING COMMAND1254206/05 21:45:53SQLAgent - Generic Refresher
dbcc inputbuffer(54)
Litespeed_log_backup_job
57 sleeping AD-ENT\XYZWPWsp4121 .msdbAWAITING COMMAND1660906/10 13:00:00SQLAgent - Job invocation engine 57 0
These are the processes i would see everytime when the job hangs. Any inputs would be appreciated.
June 10, 2009 at 10:32 pm
Hi,
By looking on the output I believe that SPID 57 is the one which is running the job (Correct me if I am wrong). Now the wait type it is showing is coming as AWAITING COMMAND and status as SLEEPING which means its waiting on something.
Ideally speaking this will not give us much info. I would recommend to run PROFILER at the back end and check what it is doing. Capture below events in the profiler
Errors & Warnings (ALL)
Performance
Degree Of Parallelism
Showplan ALL
Showplan Text
Stored Procedure
RPC:Started
RPC:Completed
SP:Starting
SP:Completed
SP:StmtStarted
SP:StmtCompleted
TSQL
SQL:BatchStarted
SQL:BatchCompleted
SQL:StmtStarted
SQL:StmtCompleted
Please also capture SYSPROCESSES output in a loop as well.
Regards
GURSETHI
June 10, 2009 at 10:39 pm
actually spid 55 is running. I did run profiler but the job hung yesterday..eben though after running profiler couldnt really capture much information since the job is at the same state since last evening..SPid 57 is some reporting services job..thanks for your reply
June 10, 2009 at 11:29 pm
Thx for reply,
If possible can u provide more details about the job? Id this job is having multiple steps then whether first step completes or not (means if its updating some tables then we can check whethere data is modified or not).
Regards
GURSETHI
June 11, 2009 at 7:12 pm
it has just one SSIS package...it hangs before hitting the SSIS package..thanks
June 11, 2009 at 7:31 pm
From you op, it appears that Litespeed is involved in the process. Have you checked the Litespeed vendor documentation or contacted them?
Max
June 11, 2009 at 8:13 pm
hmmm..actually i thought of calling them..but why wld be LiteSpeed log backup an issue? Any thoughts?
June 12, 2009 at 8:52 pm
LiteSpeed appears to have, um, quirks - just a another avenue to investigate as earlier versions caused jobs to hang when backing up to a network drive if I remember correctly. Would a SSIS maintenance task cover your requirements?
Max
June 13, 2009 at 5:08 am
Try to run the job from New query window using sp_start_job and track it .
R u scheduling and running the mentioned job or manually its executed .If manually executed then sufficient permissions must be for the job owner
June 13, 2009 at 6:55 am
Max:
We are using the latest version of lite speed. This job fails only once in a while, other days runs perfectly. Every time it fails with the same issue, like the job just starts but doesnt hits the package and stays in executing stage for ever. The processes which i mentioned in my initial are the ones i see when the job hangs.
Rajganesh:
This job is scheduled under sa. It has all the permissions. As i mentioned earlier the job runs perfect withou any issues only fails sometimes like may be once or twice in a month.
Thanks
June 17, 2009 at 11:09 am
I think we'll probably need some more information if we're going to be able to help. Any more information about the job and underlying tasks would help.
Max
June 17, 2009 at 8:44 pm
Max:
thanks for replying.. We did have the same issue today on out testing environment. Basically the job starts like when u look up in job activity monitor shows as 'Executing"...but never ends..like the job has one SSIS package..the job never hits SSIS package...this happens once in a while..really dnt know whats the issue....i am out of ideas...ders no locking nothing gng on..please help me..thansk
June 20, 2009 at 3:47 pm
In your original post you mention that the step that executes the SSIS package is not initiated from the job, how have you managed to determine this i.e. are you using logging in the SSIS package?
Max
June 21, 2009 at 8:19 am
hey max...yes i do have a log file for the SSIS package. When the jobs hangs there is nothing in the log file, like the job touches the log file bcoz i see the time the log file was changed but doesnt writes anything to the log file. Basically the job doesn't go further the point where it updates the sysjobactivity table in msdb.thanks,
June 23, 2009 at 2:52 am
Hi, I've find some interesting documentation about jobs. Maybe interesting for you:
http://www.sqlteam.com/article/controlling-jobs-in-transact-sql
http://msdn.microsoft.com/en-us/library/aa260289(SQL.80).aspx
http://www.mssqlcity.com/Articles/Adm/jobs_troubleshooting.htm
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply