Re-indexing table causes Windows to re-boot !

[Griff]

I have a DB with multiple tables and when I attempt to index one of the
tables the whole Windows servers crashes and reboots. My understanding is
that SQLServer should run as an isolated application and so should not be
able to affect the operating system.

This server has been fine for over a year now but has just started to
exhibit this problem (no recent changes or updates to the server, apart from
regular anti-virus updates, that could account for this behaviour).

The problem in more detail:

Database - 1577MB data (518 MB free). Set to automatically grow at 10% to
an un-restricted limit (data and log)

Table in question - 900808 KB data, ~7000000 rows of data

Indexes - there are three "user" indexes that I create. One of them is a
clustered index. When any of these indexes is built the server crashes.
Exhaustive DBCC tests imply that the DB and table are in perfect health.
Nothing in the event log and no memory dumps.

SQLServer - SQLServer 2000 sp3a

Windows - Win2000 sp3, IBM XSERIES_335 with 3 GHz xeon processor and oodles
of memory.

User - at wits end

Any advice would be most welcome

Thanks

Griff

 

[Danny]

Griff,

You are correct. You should focus on the operating system. Most likely SQL
Server is just taxing the IO or Processor hard and there is either a
hardware or driver issue. In the event log is it an unexpected reboot or is
there a stop error listed?

 

[Griff]

Having read the event logs (application, system, security & SQLServer
ones) and searched for a memory dmp file I get the impression that the
server crashed so fast that it simply didn't have the chance to log
anything. Metaphorically speaking, it had the rug pulled from under
it.

The event log simply lists the fact that it rebooted after an
unexpected system shutdown, but I can't find anything that implies what
could have caused the system shutdown.

Watching the task manager whilst the re-indexing is occuring (before it
dies), it shows no real sign of taxing the server's resources. Of
course now, running live without indexes, the server is as slow as a
lame dog, presumably because SQLServer is having to do lots of full
table scans.

 

[Danny]

Oddly enough I've seen this scenerio with two of our 32 way servers. We and
the hardware vendor were able to reproduce the system fault but the vendor
never could tell us what was wrong or how to fix it. Basically when the
server approached 100% CPU (doing masive qeries) the system started geting
dirty memory transfers between pods. It all started after we upgraded our
SQL Server 2000 build. We switched to 64 bit HP servers and immediately
stopped experiencing the issue. Assuming a good power supply, in my opinion
any unexpected reboot on a Windows server is one of three things.

From most likely to least likely:
1. A hardware issue
2. A poorly written driver in kernal mode
3. Windows not trapping the error.

SQL Server may be pushing the system in a new way or to 100% but that would
be a performance issue not an availability issue.
Good luck.

 

[Griff]

Well, I can't say what the problem was, but we do appear to have fixed it.

Despite nothing "appearing" to have changed on the system (we manually apply
the Microsoft critical upgrades), this problem started occuring out of the
blue.

However, it got worse throughout the day - the server started rebooting
during "normal" use, approximately ever 30 minutes. As mentioned
previously, the CPU was more or less at 100% because we couldn't rebuild the
indexes, so I was wondering whether it could be overheating or some other
symptom of the hardware being over-stressed (though it's a good spec server
so should cope).

Anyhow, that evening we clutched at straws and applied Win2000 sp4 (had been
sp3) and, would you believe it, it fixed the problem and a good nights sleep
was had by all.

 

[Robert Klemme]

Well, I can't say what the problem was, but we do appear to have
fixed it.

Despite nothing "appearing" to have changed on the system (we
manually apply the Microsoft critical upgrades), this problem started
occuring out of the blue.

However, it got worse throughout the day - the server started
rebooting during "normal" use, approximately ever 30 minutes. As
mentioned previously, the CPU was more or less at 100% because we
couldn't rebuild the indexes, so I was wondering whether it could be
overheating or some other symptom of the hardware being over-stressed
(though it's a good spec server so should cope).

Anyhow, that evening we clutched at straws and applied Win2000 sp4
(had been sp3) and, would you believe it, it fixed the problem and a
good nights sleep was had by all.

Probably someone was "joking": there are some holes in some windows
versions which allow a host to be restarted / switched off or similar via
network (these are usually closed by sp4 IIRC). So it could even be
manual intervention that caused this....

Kind regards

robert

 

[Griff]

Hi Robert

I believe that we can almost rule that one out. The server is behind a
firewall that only allows HTTP in from unknown IP addresses, everything else
is effectively blocked. The only person who can connect to it in any other
way is me.

But hey, we know that the only way to make a computer completely safe is to
switch it off.