Here's a weird one for all of you...

Hi,
Let me outline what I have and maybe someone will know how to react to what
is happening to my system.
Server: Windows2003 Standard
SQL: SQL 2005 Demo
and
SQL: SQL 2005 Processor Licensed (connecting via port 14330)
Additional Major S/W on that server: Sophos
We have run out the demo time frame and installed a 2nd instance of SQL 2005
on the same server only connecting on port 14330. I can connect to either
instance without any issues.
Problem: Sporadically (it seems to be very random) connections that are
"live" to the demo sql 2005 begin to "time out" when accessing the db for
just simple recordsets. It appears to start with one then two and then like
a wave suddenly nearly every pc hits a "time out" on a db access.
We shut down and reboot the server and all works fine again. Sometimes, if
we wait long enough, the "time outs" stop and everyone slowly gets back
connected and the issue goes away. But we obviously cannot rely on that.
This has happened 2 times in 1.5 days and we're fearful that it will just
keep happening. We installed SP1 on the new SQL 2005 instance.
Can anyone venture or hazard a guess as to what might be killing the
connection? We have looked to see if there was something happening at the
same time the time out's are occurring but find nothing.
HELP!
Thanks very much for any help,
Rich"RM" <rm@.rd.com> wrote in message
news:u9lPoWg0GHA.3476@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Let me outline what I have and maybe someone will know how to react to
> what is happening to my system.
> Server: Windows2003 Standard
> SQL: SQL 2005 Demo
> and
> SQL: SQL 2005 Processor Licensed (connecting via port 14330)
> Additional Major S/W on that server: Sophos
> We have run out the demo time frame and installed a 2nd instance of SQL
> 2005 on the same server only connecting on port 14330. I can connect to
> either instance without any issues.
> Problem: Sporadically (it seems to be very random) connections that are
> "live" to the demo sql 2005 begin to "time out" when accessing the db for
> just simple recordsets. It appears to start with one then two and then
> like a wave suddenly nearly every pc hits a "time out" on a db access.
> We shut down and reboot the server and all works fine again. Sometimes, if
> we wait long enough, the "time outs" stop and everyone slowly gets back
> connected and the issue goes away. But we obviously cannot rely on that.
> This has happened 2 times in 1.5 days and we're fearful that it will just
> keep happening. We installed SP1 on the new SQL 2005 instance.
> Can anyone venture or hazard a guess as to what might be killing the
> connection? We have looked to see if there was something happening at the
> same time the time out's are occurring but find nothing.
> HELP!
> Thanks very much for any help,
> Rich
>
I had this problem the other day (see post in
microsoft.public.sqlserver.msde). Although it was a post to the wrong group
as I was using 2005, not MSDE (2000). All of my clients timed out at 6am.
I put it down to the Virus Checker perhaps kicking in at that time and
slowing the server down to a crawl, but I find it hard to understand why it
would affect all clients like that, given that my default timeout is around
30 seconds. I didn't have this problem last night and my clients have been
happily doing their stuff for nearly 24 hours now. I asked our system admin
if there had been any network events kicking in around then and there were
none. SQL server showed no events occurred on the server and all my client
logs showed was an operation timeout error, for all of them. My system now
handles the timeout gracefully, as I don't maintain "always open"
connections - I just open a connection, execute an operation and then close
it again - so it's easier to recover from this kind of thing and "retry" the
operation.
Still I would be interested to know what might be causing this.|||Thanks for your contribution.
While we haven't definitively "found" the problem, I believe that we are on
the path to resolving it.
The issue, as I see it, is how we are managing our ado connection string and
all of the recordsets
used with that connection string. I suspect that what we are doing wrong is
not closing and then
destroying each recordset as it is no longer needed. We came from dao to ado
and access as a db
to sql 2005. So our source reflects the sloppy (easier) methods under dao to
the more strict ado
processes.
Not closing (and destroying) each recordset when it's no longer used and
letting the program exit
is, I believe, what is causing our timeout errors. We are going through
several hundred programs
to make those changes (thanks ado) and so far we've not seen a problem in
nearly 2 days, which
suggests that we're on the right path.
Anyway, thanks again for your comments and if I've suggested anything above
that might help you
I'm glad.
See ya,
R
"Robinson" <itoldyounottospamme@.nowmyinboxisfull.com> wrote in message
news:edosml$t1i$1$8300dec7@.news.demon.co.uk...
> "RM" <rm@.rd.com> wrote in message
> news:u9lPoWg0GHA.3476@.TK2MSFTNGP04.phx.gbl...
> I had this problem the other day (see post in
> microsoft.public.sqlserver.msde). Although it was a post to the wrong
> group as I was using 2005, not MSDE (2000). All of my clients timed out
> at 6am. I put it down to the Virus Checker perhaps kicking in at that time
> and slowing the server down to a crawl, but I find it hard to understand
> why it would affect all clients like that, given that my default timeout
> is around 30 seconds. I didn't have this problem last night and my
> clients have been happily doing their stuff for nearly 24 hours now. I
> asked our system admin if there had been any network events kicking in
> around then and there were none. SQL server showed no events occurred on
> the server and all my client logs showed was an operation timeout error,
> for all of them. My system now handles the timeout gracefully, as I don't
> maintain "always open" connections - I just open a connection, execute an
> operation and then close it again - so it's easier to recover from this
> kind of thing and "retry" the operation.
> Still I would be interested to know what might be causing this.
>
>
>
>
>