microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Timeout expired Procedure: ConnectionRead (recv()).

Subject: Re: Timeout expired Procedure: ConnectionRead (recv()).
From: "InvalidLastName"
Date: Wed, 22 Jun 2005 18:15:45 -0400
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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]" <[email protected]> wrote in message
news:[email protected]
> 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" <[email protected]> wrote in message
> news:[email protected]
> > 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"
> > <[email protected]> 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
> >>
> >>
> >
>
>



<Prev in Thread] Current Thread [Next in Thread>