Trapping a SQL Server Disconnect

[Arvin Meyer [MVP]]

There's a UPS only on the server (and its switch), but not on the
workstations. There's nothing in the event logs, and I'd expect
there to be warnings from the UPS software if there were power
problems.

Most American machines run on 125 volts, which is 1 leg plus ground of a 3
wire (2 wires and a ground) of the incoming power. It is quite possible that
the is a fault in 1 leg or circuit, and not on the rest. If that's the case,
you'd want a UPS at every workstation as well. I do that as a matter of
course anyway.

Not sure what you mean here. Can you explain?

Write a timestamp to a local textfile, immediately before writing the data
back to the server. If you have a timestamp in each table as well, you can
compare timestamps to find out which computer or computers are dropping the
connection.

 

[David W. Fenton]

I'll tell you what. Google for "SQL-Server database corruption"
and start reading.

I don't doubt that SQL Server can't corrupt.

What I find dubious is the claim that anything happening with regard
to the client connection can cause the corruption. It seems obvious
to me that only failures on the server end could ever corrupt the
data store.

 

[David W. Fenton]

Most American machines run on 125 volts, which is 1 leg plus
ground of a 3 wire (2 wires and a ground) of the incoming power.
It is quite possible that the is a fault in 1 leg or circuit, and
not on the rest. If that's the case, you'd want a UPS at every
workstation as well. I do that as a matter of course anyway.

None of the workstations show any signs of power issues, nor does
the server.

Write a timestamp to a local textfile, immediately before writing
the data back to the server. If you have a timestamp in each table
as well, you can compare timestamps to find out which computer or
computers are dropping the connection.

I know which computers drop the connections, the three that the
people who are in the office every day and using the database
application are using.

I don't see what information your suggested course of action
provides me that I don't already have.

 

[Arvin Meyer [MVP]]

I don't doubt that SQL Server can't corrupt.

What I find dubious is the claim that anything happening with regard
to the client connection can cause the corruption. It seems obvious
to me that only failures on the server end could ever corrupt the
data store.

One of the things we did to try to nail down the problem was to retrieve the
Windows 2003 Server Event logs. There was nothing apparent. We did reboot
the server, just in case.

 

[David W. Fenton]

One of the things we did to try to nail down the problem was to
retrieve the Windows 2003 Server Event logs. There was nothing
apparent. We did reboot the server, just in case.

I'm not seeing anything at all in the event logs that looks
problematic. In fact, there are fewer error events in this log than
I'm used to seeing! I guess that comes from being a server that's
been in operation for a long time -- the kinks are ironed out.

I really think the problem is likely software related, and possibly
due to the decomissioning of Exchange on this server. It used to the
the client's email server, but after problems with getting their
Internet access up and running after their move to new offices in
February, they now outsource their Exchange hosting. And the first
reports of disconnects were from Outlook disconnecting from the
remote Exchange host. But that has stopped, so it doesn't appear to
be a problem with the network (and we went through a lot of
troubleshooting with the techs from the Exchange hosting service,
who were top-notch).

But I suspect some kind of crud hanging around from Exchange that
might be interfering with the network redirector (I've seen that
before, in fact) -- and my Exchange guy is supposed to be looking
into it.

Your mentioning of a reboot was a good reminder -- I don't see that
the server had been rebooted in quite a long time (I couldn't find
the last boot time), so I just rebooted it. It may have been up a
very, very long time, and that could be part of the problem.

Thanks for all your suggestions -- I know I haven't sounded too
grateful, but every little suggestion causes me to think of things
to look at, so it's very helpful to have the feedback.

 

[Arvin Meyer [MVP]]

Hopefully, rebooting will work. It often does. I usually reboot servers once
a month during server maintenance as a matter of course. It usually cleans
out crap such as minor memory leaks. I also, back up and clear the logs. I
don't do much admin work any more, so my practices may be conservative.

Back in the days of NT 4.0 when I was a certified admin, we used to
reinstall the server software often, as a fix for problems (especially with
SP2, which was a nightmare) There were, IIRC, also problems with SP4 or 5
with Exchange.

If nothing else works, I'd seriously consider, taking down and formatting
the harddrives then reinstalling the OS without enabling Exchange in the
first place. Keep in mind that it takes an entire weekend to do it properly,
and you do need to back up all the Active Directory settings or it will take
much longer. It's a drastic move, and I'd seriously consider even buying a
new server before I'd do it, since it ultimately may save money. The last
good SBS server I bought cost about $2400 and took about 5 hours to set up,
including rebuilding all the accounts from scratch. Time-wise it cost the
client only a few hundred more for everything over just rebuilding the old
server.

 

[david]

I just remembered something else I always wanted to try :~)

When Jet connects to ODBC, it first attempts to connect
using the Jet login name and password. (by default Admin
and blank). I've always wondered if this was interfering
with the re-connect process. It's easy to check - just turn off
JetTryAuth in the registry - but I've never had the opportunity.

(david)

I've never heard of anyone being able to successfully recover
from a connection timeout, so I'm not hopeful about recovering
from a dropped connection.

The form error handler handles data errors on bound forms.
Do you have a form bound to an ODBC data source?
Try this:
Put a timer on the form, and check the recordsetclone
StillExecuting property. If it is true, cancel the task.

Will it work? See paragraph 1.

(david)

 

[David W. Fenton]

If nothing else works, I'd seriously consider, taking down and
formatting the harddrives then reinstalling the OS without
enabling Exchange in the first place.

I had forgotten that this server's OS installation was rebuilt
scratch a little over a year ago, just before I upsized the app to
SQL Server. But it ran perfectly in the old location, so I really
think the problem is something to do with the new office.

Keep in mind that it takes an entire weekend to do it properly,

I'm well aware of it!

and you do need to back up all the Active Directory settings or it
will take much longer. It's a drastic move, and I'd seriously
consider even buying a new server before I'd do it, since it
ultimately may save money. The last good SBS server I bought cost
about $2400 and took about 5 hours to set up, including rebuilding
all the accounts from scratch. Time-wise it cost the client only a
few hundred more for everything over just rebuilding the old
server.

The other guy involved in troubleshooting switched the thing over to
use the onboard gigabit NIC, which had been unused, and connected to
the new switch, the users haven't reported a disconnect in the last
two business days. The new switch didn't solve the problem with the
old NIC, so it appears to have been a problem with the old NIC.

I'm keeping my fingers crossed!

 

[David W. Fenton]

I just remembered something else I always wanted to try :~)

When Jet connects to ODBC, it first attempts to connect
using the Jet login name and password. (by default Admin
and blank). I've always wondered if this was interfering
with the re-connect process. It's easy to check - just turn off
JetTryAuth in the registry - but I've never had the opportunity.

Actually, in this app, the Jet username is the same as the user's
Window logon, though the Jet password is blank. Not sure what
happens then. I am definitely using Windows authorization with the
SQL Server.

So far, since the switch to the gigabit NIC, there have been no
reports of disconnects.

Keeping my fingers crossed!