|
Home > Archive > MS SQL Server > December 2006 > A Real Sql Server 2005 Bug! A Real Sql Server Bug!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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 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
| |
| Tracy McKibben 2006-12-12, 7:12 pm |
| Mike 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 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
>
>
>
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
| |
| Leo Giakoumakis [MS] 2006-12-12, 7:12 pm |
| Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF- FC5A8D309F47@microso
ft.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
>
>
>
>
>
>
>
|
|
|
|
|