July 23, 2013 at 4:03 am
Hi,
I have a job which runs every week which archieves records in to another database. When i run the SP in the query window it takes hardly 15 mins where as when i run the same through JOb it is not completing successfully for more than 15 hrs . So i need to manually stop the job every week.
There are two step,
first oe 1) Archieve
2) Re-index.
I checked possible solns and everything is fine such as
1) Permission issues(Job owner is Sysadmin)
2)Blocking(No blocking when the Job run's)
3)No clash in schedule Jobs'.
Can anyone help if possible in this issue.
July 23, 2013 at 4:12 am
Hi
Is there any message in job history log?
Br.
Mike
July 23, 2013 at 4:22 am
Hi Mike,
Since i stop the job to avoid issue's in production there is no history maintained. The Job is not getting failed also as it keeps on running.
July 23, 2013 at 7:30 am
There could several causes of this but the most likely is parameter sniffing. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
Can you post the proc? We may want to see the tables and index schemes but let's start with just the stored proc code so we can help eliminate parameter sniffing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2013 at 8:41 am
HI,
There is no parameter for this stored procedure,so parameter sniffing is not the issue i hope. When i execute only the stored procedure it gets executed.
July 23, 2013 at 9:11 am
It could be a security issue. You and the login that is running SQL Agent may not have the same privileges. It may be a settings issue such as the ANSI connection settings being different from your machine than from the default on the server. Past that, without knowing more about everything I'm out of guesses.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2013 at 1:50 pm
It would help if you could share the text for the first job step (which I assume is the one that gets hung).
If you want a suggested out of the blue, put this first in the job step:
SET QUOTED_IDENTIFIER ON
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply