Tuesday, March 6, 2012

A Real Sql Server 2005 Bug! A Real Sql Server Bug!

A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing t
o
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpecte
d
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
MikeMike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
we
> are getting the folllowing message every few nights causing our processing
to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpec
ted
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumb
er
> When this simplified query is run from the management studio, it fails abo
ut
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>

No comments:

Post a Comment