Please help- hairloss imminent (ProcessingAbortedException)

Hi,

I hope someone can help with this one!

We have a report which runs fine interactively - no matter which range we set it to run against, but when run as a subscription it failes. Taking a closer look at the logs, I get the following info :

ReportingServicesService!processing!1ac!1/3/2006-08:22:24:: w WARN: Data source 'DWOR': Report processing has been aborted.
ReportingServicesService!processing!1ac!1/3/2006-08:22:24:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. > System.Data.SqlClient.SqlException: Operation cancelled by user.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.ReportProcessing.a1.v()
End of inner exception stack trace
at Microsoft.ReportingServices.ReportProcessing.a1.v()
at Microsoft.ReportingServices.ReportProcessing.ax.c()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Boolean& A_0)
at Microsoft.ReportingServices.ReportProcessing.ax.b()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Object A_0)
End of inner exception stack trace
ReportingServicesService!library!1ac!01/03/2006-08:22:24:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
ReportingServicesService!subscription!1ac!01/03/2006-08:22:24:: Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. > System.Data.SqlClient.SqlException: Operation cancelled by user.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.ReportProcessing.a1.v()
End of inner exception stack trace
at Microsoft.ReportingServices.ReportProcessing.a1.v()
at Microsoft.ReportingServices.ReportProcessing.ax.c()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Boolean& A_0)
at Microsoft.ReportingServices.ReportProcessing.ax.b()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Object A_0)
End of inner exception stack trace
at Microsoft.ReportingServices.ReportProcessing.b.c()
at Microsoft.ReportingServices.ReportProcessing.ab.ag()
at Microsoft.ReportingServices.ReportProcessing.bb.b(ParameterInfoCollection A_0, Boolean A_1)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4, DateTime A_5, CreateReportChunk A_6, ab& A_7)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
End of inner exception stack trace
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.SaveReport(Notification notification, SubscriptionData data)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.Deliver(Notification notification)

I've checked everything I can think of, so far no luck. I've also run the report in the background as different users, still no joy.

The bizarre thing is it's been working fine up until a couple of weeks ago, then all of a sudden this occurs.

Any help much appreciated!

Cheers,

Tony

Managed to resolve the issue myself in the end! I noticed that in the RunningJobs table within the RS database, the timeout was set to the default of 1800 seconds. Strangely enough that's how long it took for the above error to occur. I've changed the report target properties execution settings to 5400, and that has done the trick.

Talk about misleading error messages - no timeouts mentioned in the log files!

Hope this helps others out.

|||

I've been having a not too different problem, I'm just trying to find the "report taget properties execution settings"... where are they found?

Please help- hairloss imminent (ProcessingAbortedException)

Hi,

I hope someone can help with this one!

We have a report which runs fine interactively - no matter which range we set it to run against, but when run as a subscription it failes. Taking a closer look at the logs, I get the following info :

ReportingServicesService!processing!1ac!1/3/2006-08:22:24:: w WARN: Data source 'DWOR': Report processing has been aborted.
ReportingServicesService!processing!1ac!1/3/2006-08:22:24:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. > System.Data.SqlClient.SqlException: Operation cancelled by user.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.ReportProcessing.a1.v()
End of inner exception stack trace
at Microsoft.ReportingServices.ReportProcessing.a1.v()
at Microsoft.ReportingServices.ReportProcessing.ax.c()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Boolean& A_0)
at Microsoft.ReportingServices.ReportProcessing.ax.b()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Object A_0)
End of inner exception stack trace
ReportingServicesService!library!1ac!01/03/2006-08:22:24:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
ReportingServicesService!subscription!1ac!01/03/2006-08:22:24:: Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. > System.Data.SqlClient.SqlException: Operation cancelled by user.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.ReportProcessing.a1.v()
End of inner exception stack trace
at Microsoft.ReportingServices.ReportProcessing.a1.v()
at Microsoft.ReportingServices.ReportProcessing.ax.c()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Boolean& A_0)
at Microsoft.ReportingServices.ReportProcessing.ax.b()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Object A_0)
End of inner exception stack trace
at Microsoft.ReportingServices.ReportProcessing.b.c()
at Microsoft.ReportingServices.ReportProcessing.ab.ag()
at Microsoft.ReportingServices.ReportProcessing.bb.b(ParameterInfoCollection A_0, Boolean A_1)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, az A_4, DateTime A_5, CreateReportChunk A_6, ab& A_7)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(DateTime A_0, GetReportChunk A_1, ProcessingContext A_2, RenderingContext A_3, CreateReportChunk A_4, Boolean& A_5)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
End of inner exception stack trace
at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.SaveReport(Notification notification, SubscriptionData data)
at Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareProvider.Deliver(Notification notification)

I've checked everything I can think of, so far no luck. I've also run the report in the background as different users, still no joy.

The bizarre thing is it's been working fine up until a couple of weeks ago, then all of a sudden this occurs.

Any help much appreciated!

Cheers,

Tony

Managed to resolve the issue myself in the end! I noticed that in the RunningJobs table within the RS database, the timeout was set to the default of 1800 seconds. Strangely enough that's how long it took for the above error to occur. I've changed the report target properties execution settings to 5400, and that has done the trick.

Talk about misleading error messages - no timeouts mentioned in the log files!

Hope this helps others out.

|||

I've been having a not too different problem, I'm just trying to find the "report taget properties execution settings"... where are they found?

PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >>> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION?
Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA
|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process?) and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
==========================================
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegro ups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>
|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process?) and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION'Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process') and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
=========================================="Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegroups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process') and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com