| Groups | Blog | Home |
sql server notification services : Timeout expired Procedure: ConnectionRead (recv()).
10,000 events, the SQL-NS failed to generate the notifications, and the following error was logged in the event log: ---------------------------------------------------------------------------- ------------------------------------------------- <NotificationServicesEvent> <Description> The view of the event class could not be materialized. </Description> <Context> <EventParameters> <InstanceName>SQLNSDemoInstance</InstanceName> <ApplicationName>SQLNS</ApplicationName> <StoredProcedureName>NSMaterializeSQLNSDemoEventsEventViews</StoredProcedure Name> <EventClassName>SQLNSDemoEvents</EventClassName> <QuantumId>14451</QuantumId> <EventThrottle>500000</EventThrottle> </EventParameters> <SqlServerError> Source: .Net SqlClient Data Provider Number: -2 State: 0 Class: 10 Server: (our database sever) Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Procedure: ConnectionRead (recv()). Line Number: 0 </SqlServerError> </Context> <Description> Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. </Description> <Instance>SQLNSDemoInstance</Instance> <ApplicationName>SQLNS</ApplicationName> <Component>Generator</Component> <Thread>146</Thread> </NotificationServicesEvent> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. ---------------------------------------------------------------------------- ------------------------------------------------- Here are some search results from google http://www.sqltalk.org/ftopic22722.html ( this is related to distributor, but our case is on generator instead) http://support.microsoft.com/default.aspx?scid=kb;en-us;229564&Product=sql2k However, in our case when the error was logged, the database is still accessible. It doesn't look like the database server is under extreme heavy load at the time. Is there a way to identify what really caused the issue ? (Is this ADO command timeout or connection out?) Any resolution for this ? (change ADF or SQLNS .config file) Also, when such error occurred, how can we re-generate these notifications ? Thanks ILN
Hi -
My understanding of your post is that the rule that generates the notifications over large batches is timing out. Right? Some thoughts to consider: Do you have any indexes (defined using T-SQL statements in the ADF) for the subscription and events tables? If not, that's probably where I'd start. Creating an index on the base tables will help improve the performance of the dynamically created views. Are the rules joining any linked servers? If so, that sometimes creates less than optimal performance. You can also adjust the ActionTimeout element in the rule declaration, but the default 30 minutes is typically enough. I'd look for optimization techniques first. HTH.... -- Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 I support PASS, the Professional Association for SQL Server. (www.sqlpass.org) On Mon, 20 Jun 2005 18:38:59 -0400, "InvalidLastName" [quoted text, click to view] <invalidlastname@toomanyvalidations.page> wrote:
>From time to time, where there was large event batch, which may contain over >10,000 events, the SQL-NS failed to generate the notifications, and the >following error was logged in the event log: > >---------------------------------------------------------------------------- >------------------------------------------------- > ><NotificationServicesEvent> > <Description> > The view of the event class could not be materialized. > </Description> > <Context> ><EventParameters> > <InstanceName>SQLNSDemoInstance</InstanceName> > <ApplicationName>SQLNS</ApplicationName> > ><StoredProcedureName>NSMaterializeSQLNSDemoEventsEventViews</StoredProcedure >Name> > <EventClassName>SQLNSDemoEvents</EventClassName> > <QuantumId>14451</QuantumId> > <EventThrottle>500000</EventThrottle> ></EventParameters> > ><SqlServerError> > Source: .Net SqlClient Data Provider > Number: -2 > State: 0 > Class: 10 > Server: (our database sever) > Message: Timeout expired. The timeout period elapsed prior to completion >of the operation or the server is not responding. > Procedure: ConnectionRead (recv()). > Line Number: 0 ></SqlServerError> > > </Context> > <Description> > Timeout expired. The timeout period elapsed prior to completion of the >operation or the server is not responding. > </Description> > <Instance>SQLNSDemoInstance</Instance> > <ApplicationName>SQLNS</ApplicationName> > <Component>Generator</Component> > <Thread>146</Thread> ></NotificationServicesEvent> > >For more information, see Help and Support Center at >http://go.microsoft.com/fwlink/events.asp. > >---------------------------------------------------------------------------- >------------------------------------------------- > >Here are some search results from google > >http://www.sqltalk.org/ftopic22722.html ( this is related to distributor, >but our case is on generator instead) > >http://support.microsoft.com/default.aspx?scid=kb;en-us;229564&Product=sql2k > >However, in our case when the error was logged, the database is still >accessible. It doesn't look like the database server is under extreme heavy >load at the time. > >Is there a way to identify what really caused the issue ? (Is this ADO >command timeout or connection out?) Any resolution for this ? (change ADF or >SQLNS .config file) > >Also, when such error occurred, how can we re-generate these notifications ? > >Thanks > >ILN > >
It's not the rule that's timing out, but rather the NS stored procedure that
materializes the event view. To the original poster: are you using NS2.0 on SQL 2000 or using the SQL 2005 version of NS? Thanks -shyam -- Learn more about SQL-NS: http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ --------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. --------------------------------------------- [quoted text, click to view] "Joe Webb" <joew@webbtechsolutions.com> wrote in message news:sehib195vnc0nvl46u67dbffp8n5lumbpb@4ax.com... > Hi - > > My understanding of your post is that the rule that generates the > notifications over large batches is timing out. Right? > > Some thoughts to consider: > > Do you have any indexes (defined using T-SQL statements in the ADF) > for the subscription and events tables? If not, that's probably where > I'd start. Creating an index on the base tables will help improve the > performance of the dynamically created views. > > Are the rules joining any linked servers? If so, that sometimes > creates less than optimal performance. > > You can also adjust the ActionTimeout element in the rule declaration, > but the default 30 minutes is typically enough. I'd look for > optimization techniques first. > > HTH.... > > -- > Joe Webb > SQL Server MVP > > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > I support PASS, the Professional Association for SQL Server. > (www.sqlpass.org) > > > On Mon, 20 Jun 2005 18:38:59 -0400, "InvalidLastName" > <invalidlastname@toomanyvalidations.page> wrote: > >>From time to time, where there was large event batch, which may contain >>over >>10,000 events, the SQL-NS failed to generate the notifications, and the >>following error was logged in the event log: >> >>---------------------------------------------------------------------------- >>------------------------------------------------- >> >><NotificationServicesEvent> >> <Description> >> The view of the event class could not be materialized. >> </Description> >> <Context> >><EventParameters> >> <InstanceName>SQLNSDemoInstance</InstanceName> >> <ApplicationName>SQLNS</ApplicationName> >> >><StoredProcedureName>NSMaterializeSQLNSDemoEventsEventViews</StoredProcedure >>Name> >> <EventClassName>SQLNSDemoEvents</EventClassName> >> <QuantumId>14451</QuantumId> >> <EventThrottle>500000</EventThrottle> >></EventParameters> >> >><SqlServerError> >> Source: .Net SqlClient Data Provider >> Number: -2 >> State: 0 >> Class: 10 >> Server: (our database sever) >> Message: Timeout expired. The timeout period elapsed prior to completion >>of the operation or the server is not responding. >> Procedure: ConnectionRead (recv()). >> Line Number: 0 >></SqlServerError> >> >> </Context> >> <Description> >> Timeout expired. The timeout period elapsed prior to completion of the >>operation or the server is not responding. >> </Description> >> <Instance>SQLNSDemoInstance</Instance> >> <ApplicationName>SQLNS</ApplicationName> >> <Component>Generator</Component> >> <Thread>146</Thread> >></NotificationServicesEvent> >> >>For more information, see Help and Support Center at >>http://go.microsoft.com/fwlink/events.asp. >> >>---------------------------------------------------------------------------- >>------------------------------------------------- >> >>Here are some search results from google >> >>http://www.sqltalk.org/ftopic22722.html ( this is related to distributor, >>but our case is on generator instead) >> >>http://support.microsoft.com/default.aspx?scid=kb;en-us;229564&Product=sql2k >> >>However, in our case when the error was logged, the database is still >>accessible. It doesn't look like the database server is under extreme >>heavy >>load at the time. >> >>Is there a way to identify what really caused the issue ? (Is this ADO >>command timeout or connection out?) Any resolution for this ? (change ADF >>or >>SQLNS .config file) >> >>Also, when such error occurred, how can we re-generate these notifications >>? >> >>Thanks >> >>ILN >> >> >
we are using v2.0.3008.0 SQLNS Enterprise version sp1 with SQL 2000. I
believe we have all the proper indexes. As Shyam mentioned, it looks like it got timeout before the rule fired Actually I looked at the contents of the sp which shown in the error log, and saw the first line is to acquire the application lock (I didn't find anywhere in the stored procedure to release the lock. from committing/rollback transaction perhaps?). Is it possible somewhere in the application still holding the lock, or the lock was not properly released from previous process ? I am not sure this is just a coincident or not, our SQLNS application ran very well when we stress tested it, e.g. continually submitted batches which contain 10,000+ events, or 1,000+ single-event batches. However, if it has been idle for a while without processing any large batches, I usually got such error. This is just from my observations. If this error was due to query (or command) timeout, is there a setting that I can change the default command timeout value for SQLNS ? Thanks BTW, If I need SQLNS re-generate the notifications for those events, which are failed at first run, is it a right practice to just move the event collection time to current quantum, so they will be picked up again ? ILN [quoted text, click to view] "Shyam Pather [MSFT]" <spather@online.microsoft.com> wrote in message news:O$6O$p1dFHA.3836@tk2msftngp13.phx.gbl... > It's not the rule that's timing out, but rather the NS stored procedure that > materializes the event view. To the original poster: are you using NS2.0 on > SQL 2000 or using the SQL 2005 version of NS? > > Thanks > -shyam > > -- > Learn more about SQL-NS: > http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ > --------------------------------------------- > This posting is provided "AS IS" with no warranties, and confers no rights. > Use of included script samples are subject to the terms specified at > http://www.microsoft.com/info/cpyright.htm. > --------------------------------------------- > "Joe Webb" <joew@webbtechsolutions.com> wrote in message > news:sehib195vnc0nvl46u67dbffp8n5lumbpb@4ax.com... > > Hi - > > > > My understanding of your post is that the rule that generates the > > notifications over large batches is timing out. Right? > > > > Some thoughts to consider: > > > > Do you have any indexes (defined using T-SQL statements in the ADF) > > for the subscription and events tables? If not, that's probably where > > I'd start. Creating an index on the base tables will help improve the > > performance of the dynamically created views. > > > > Are the rules joining any linked servers? If so, that sometimes > > creates less than optimal performance. > > > > You can also adjust the ActionTimeout element in the rule declaration, > > but the default 30 minutes is typically enough. I'd look for > > optimization techniques first. > > > > HTH.... > > > > -- > > Joe Webb > > SQL Server MVP > > > > > > ~~~ > > Get up to speed quickly with SQLNS > > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > I support PASS, the Professional Association for SQL Server. > > (www.sqlpass.org) > > > > > > On Mon, 20 Jun 2005 18:38:59 -0400, "InvalidLastName" > > <invalidlastname@toomanyvalidations.page> wrote: > > > >>From time to time, where there was large event batch, which may contain > >>over > >>10,000 events, the SQL-NS failed to generate the notifications, and the > >>following error was logged in the event log: > >> > >>-------------------------------------------------------------------------- -- > >>------------------------------------------------- > >> > >><NotificationServicesEvent> > >> <Description> > >> The view of the event class could not be materialized. > >> </Description> > >> <Context> > >><EventParameters> > >> <InstanceName>SQLNSDemoInstance</InstanceName> > >> <ApplicationName>SQLNS</ApplicationName> > >> > >><StoredProcedureName>NSMaterializeSQLNSDemoEventsEventViews</StoredProcedu re > >>Name> > >> <EventClassName>SQLNSDemoEvents</EventClassName> > >> <QuantumId>14451</QuantumId> > >> <EventThrottle>500000</EventThrottle> > >></EventParameters> > >> > >><SqlServerError> > >> Source: .Net SqlClient Data Provider > >> Number: -2 > >> State: 0 > >> Class: 10 > >> Server: (our database sever) > >> Message: Timeout expired. The timeout period elapsed prior to completion > >>of the operation or the server is not responding. > >> Procedure: ConnectionRead (recv()). > >> Line Number: 0 > >></SqlServerError> > >> > >> </Context> > >> <Description> > >> Timeout expired. The timeout period elapsed prior to completion of the > >>operation or the server is not responding. > >> </Description> > >> <Instance>SQLNSDemoInstance</Instance> > >> <ApplicationName>SQLNS</ApplicationName> > >> <Component>Generator</Component> > >> <Thread>146</Thread> > >></NotificationServicesEvent> > >> > >>For more information, see Help and Support Center at > >>http://go.microsoft.com/fwlink/events.asp. > >> > >>-------------------------------------------------------------------------- -- > >>------------------------------------------------- > >> > >>Here are some search results from google > >> > >>http://www.sqltalk.org/ftopic22722.html ( this is related to distributor, > >>but our case is on generator instead) > >> > >>http://support.microsoft.com/default.aspx?scid=kb;en-us;229564&Product=sql 2k > >> > >>However, in our case when the error was logged, the database is still > >>accessible. It doesn't look like the database server is under extreme > >>heavy > >>load at the time. > >> > >>Is there a way to identify what really caused the issue ? (Is this ADO > >>command timeout or connection out?) Any resolution for this ? (change ADF > >>or > >>SQLNS .config file) > >> > >>Also, when such error occurred, how can we re-generate these notifications > >>? > >> > >>Thanks > >> > >>ILN > >> > >> > > > >
Hi,
We still kept getting this error. Is there any way to modify the timeout value for query/command timeout and event. Is it possible because my scheduled event provider ran too long and didn't release the application ? I would like to know how can I modify certain timeout value, such as "query/command timeout (I think default is 30 seconds in ADO.NET), and event provider run timeout Thanks ILN [quoted text, click to view] "InvalidLastName" <invalidlastname@toomanyvalidations.page> wrote in message news:ukF6AU$dFHA.3808@TK2MSFTNGP14.phx.gbl... > we are using v2.0.3008.0 SQLNS Enterprise version sp1 with SQL 2000. I > believe we have all the proper indexes. As Shyam mentioned, it looks like it > got timeout before the rule fired > > Actually I looked at the contents of the sp which shown in the error log, > and saw the first line is to acquire the application lock (I didn't find > anywhere in the stored procedure to release the lock. from > committing/rollback transaction perhaps?). Is it possible somewhere in the > application still holding the lock, or the lock was not properly released > from previous process ? > > I am not sure this is just a coincident or not, our SQLNS application ran > very well when we stress tested it, e.g. continually submitted batches which > contain 10,000+ events, or 1,000+ single-event batches. However, if it has > been idle for a while without processing any large batches, I usually got > such error. This is just from my observations. > > If this error was due to query (or command) timeout, is there a setting that > I can change the default command timeout value for SQLNS ? > > Thanks > > BTW, > If I need SQLNS re-generate the notifications for those events, which are > failed at first run, is it a right practice to just move the event > collection time to current quantum, so they will be picked up again ? > > ILN > > > > "Shyam Pather [MSFT]" <spather@online.microsoft.com> wrote in message > news:O$6O$p1dFHA.3836@tk2msftngp13.phx.gbl... > > It's not the rule that's timing out, but rather the NS stored procedure > that > > materializes the event view. To the original poster: are you using NS2.0 > on > > SQL 2000 or using the SQL 2005 version of NS? > > > > Thanks > > -shyam > > > > -- > > Learn more about SQL-NS: > > http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ > > --------------------------------------------- > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > Use of included script samples are subject to the terms specified at > > http://www.microsoft.com/info/cpyright.htm. > > --------------------------------------------- > > "Joe Webb" <joew@webbtechsolutions.com> wrote in message > > news:sehib195vnc0nvl46u67dbffp8n5lumbpb@4ax.com... > > > Hi - > > > > > > My understanding of your post is that the rule that generates the > > > notifications over large batches is timing out. Right? > > > > > > Some thoughts to consider: > > > > > > Do you have any indexes (defined using T-SQL statements in the ADF) > > > for the subscription and events tables? If not, that's probably where > > > I'd start. Creating an index on the base tables will help improve the > > > performance of the dynamically created views. > > > > > > Are the rules joining any linked servers? If so, that sometimes > > > creates less than optimal performance. > > > > > > You can also adjust the ActionTimeout element in the rule declaration, > > > but the default 30 minutes is typically enough. I'd look for > > > optimization techniques first. > > > > > > HTH.... > > > > > > -- > > > Joe Webb > > > SQL Server MVP > > > > > > > > > ~~~ > > > Get up to speed quickly with SQLNS > > > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > > I support PASS, the Professional Association for SQL Server. > > > (www.sqlpass.org) > > > > > > > > > On Mon, 20 Jun 2005 18:38:59 -0400, "InvalidLastName" > > > <invalidlastname@toomanyvalidations.page> wrote: > > > > > >>From time to time, where there was large event batch, which may contain > > >>over > > >>10,000 events, the SQL-NS failed to generate the notifications, and the > > >>following error was logged in the event log: > > >> > > > >>-------------------------------------------------------------------------- > -- > > >>------------------------------------------------- > > >> > > >><NotificationServicesEvent> > > >> <Description> > > >> The view of the event class could not be materialized. > > >> </Description> > > >> <Context> > > >><EventParameters> > > >> <InstanceName>SQLNSDemoInstance</InstanceName> > > >> <ApplicationName>SQLNS</ApplicationName> > > >> > > > >><StoredProcedureName>NSMaterializeSQLNSDemoEventsEventViews</StoredProcedu > re > > >>Name> > > >> <EventClassName>SQLNSDemoEvents</EventClassName> > > >> <QuantumId>14451</QuantumId> > > >> <EventThrottle>500000</EventThrottle> > > >></EventParameters> > > >> > > >><SqlServerError> > > >> Source: .Net SqlClient Data Provider > > >> Number: -2 > > >> State: 0 > > >> Class: 10 > > >> Server: (our database sever) > > >> Message: Timeout expired. The timeout period elapsed prior to > completion > > >>of the operation or the server is not responding. > > >> Procedure: ConnectionRead (recv()). > > >> Line Number: 0 > > >></SqlServerError> > > >> > > >> </Context> > > >> <Description> > > >> Timeout expired. The timeout period elapsed prior to completion of > the > > >>operation or the server is not responding. > > >> </Description> > > >> <Instance>SQLNSDemoInstance</Instance> > > >> <ApplicationName>SQLNS</ApplicationName> > > >> <Component>Generator</Component> > > >> <Thread>146</Thread> > > >></NotificationServicesEvent> > > >> > > >>For more information, see Help and Support Center at > > >>http://go.microsoft.com/fwlink/events.asp. > > >> > > > >>-------------------------------------------------------------------------- > -- > > >>------------------------------------------------- > > >> > > >>Here are some search results from google > > >> > > >>http://www.sqltalk.org/ftopic22722.html ( this is related to > distributor, > > >>but our case is on generator instead) > > >> > > > >>http://support.microsoft.com/default.aspx?scid=kb;en-us;229564&Product=sql > 2k > > >> > > >>However, in our case when the error was logged, the database is still > > >>accessible. It doesn't look like the database server is under extreme > > >>heavy > > >>load at the time. > > >> > > >>Is there a way to identify what really caused the issue ? (Is this ADO > > >>command timeout or connection out?) Any resolution for this ? (change > ADF > > >>or > > >>SQLNS .config file) > > >> > > >>Also, when such error occurred, how can we re-generate these > notifications > > >>? > > >> > > >>Thanks > > >> > > >>ILN > > >> > > >> > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |