May 28, 2013 at 2:18 pm
Hello - I have a stored proc that's not completing.
I ran it once before and it completed in 1.5 hours but then I ran the same proc on Friday and it was still running when I came into work this morning (> 3 days).
Have you ever encountered this behavior with a stored proc? Do you have any first impressions about what might be causing the stored proc not to complete? What next steps would you take to debug and fix the root cause?
May 28, 2013 at 2:33 pm
sqlguy-736318 (5/28/2013)
Hello - I have a stored proc that's not completing.I ran it once before and it completed in 1.5 hours but then I ran the same proc on Friday and it was still running when I came into work this morning (> 3 days).
Have you ever encountered this behavior with a stored proc? Do you have any first impressions about what might be causing the stored proc not to complete? What next steps would you take to debug and fix the root cause?
Would need a bit more in the way of details to have much chance here. It could be so many things. Without some more knowledge of the tables and the proc even a guess is not worth the effort.
_______________________________________________________________
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/
May 28, 2013 at 2:41 pm
Might be this
Or might not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2013 at 3:04 pm
the stored proc hangs in section 4 of the attached proc
May 28, 2013 at 3:16 pm
sqlguy-736318 (5/28/2013)
the stored proc hangs in section 4 of the attached proc
----------------------
-- #4 - Now update the CRM CEB table
----------------------
DECLARE @Rows int = 1
--DECLARE @RowPrint int
DECLARE @BatchSize int = 5000
WHILE @Rows > 0
BEGIN
update top(@BatchSize) ceb
set ceb.new_wcdscreatedon = i.CreateDate
from contactextensionbase ceb
left join #tmpIndividualCreateDates i on ceb.New_IndividualId = i.IndividualID
where ceb.new_wcdscreatedon is null
SET @Rows = @@ROWCOUNT
END
SET @Msg = 'Completed WCDS CreatedOn Update'
RAISERROR (@Msg, 0, 1) WITH NOWAIT
Do you have any rows in #tmpIndividualCreateDates where CreateDate is NULL?
The declaration of that temp table allows for NULL in that column and you fill it from an external file. If you have a NULL in any row in that temp table you have an endless loop.
_______________________________________________________________
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/
May 28, 2013 at 3:56 pm
Do you have any rows in #tmpIndividualCreateDates where CreateDate is NULL?
The declaration of that temp table allows for NULL in that column and you fill it from an external file. If you have a NULL in any row in that temp table you have an endless loop.
I just ran the query to populate the temp table and it has no rows where CreateDate is null
May 28, 2013 at 4:08 pm
Let us take a close look at the code:
update top(@BatchSize) ceb set
ceb.new_wcdscreatedon = i.CreateDate
from
contactextensionbase ceb
left join #tmpIndividualCreateDates i
on ceb.New_IndividualId = i.IndividualID
where
ceb.new_wcdscreatedon is null
You are doing a LEFT OUTER JOIN from contactextensionbase to #tmpIndividualCreateDates. This means you are returning ALL rows from contactextensionbase whether there is a matching row in #tmpIndividualCreateDates or not. If those rows that do not have a matching row in the temp table and new_wcdscreatedon is null, you are populating it with null. Looks like an infinite loop to me.
May 28, 2013 at 5:32 pm
ok - I removed the "left" join and I am currently rerunning the query
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply