Problem after 49.71 Days

From: Lockdesign Ltd (GeoffKellLockdesignLtd_at_discussions.microsoft.com)
Date: 02/28/05 

Date: Mon, 28 Feb 2005 06:11:02 -0800

I have raised this question previously but now have additional information.

We have a SQL Server 7 machine running under Windows 2000 on a HP Compaq
Proliant ML370/G2.

The system runs fine until 49.71 days (the time it takes for the
GetTickCount API to wrap back to 0. i.e. max no milliseconds in 32 bit
quantity).

After this we start to get timeouts when reading and writing to the
database. The timeouts start immediately after the 49.71 days (within a
couple of seconds) and continue until we reboot the database machine.
Rebooting all the clients does not stop the problem occuring once the clients
restart and reconnect; we **MUST** reboot the actual SQL Server machine.

We considered the possibility that the problem could be caused by clients
holding connections open to the database for the 49.71 days but have taken
steps to ensure that connections are not held open this long. We also noticed
that we had 'dangling connections' (connections shown as open but where the
owner had been rebooted, etc). We have taken steps to clear these connections
down.

Now just over 50 days ago we went through the 49.71 days without any
problems. However, our plant was not running at this point and therefore
there were very few database transactions in progress. On Friday night we hit
the next 49.71 days (99.42), the plant was running and sure enough, 2 seconds
after the 99.42 days passed we started getting timeouts. I noticed that there
were 100% spikes in CPU utilisation on the database machine that corresponded
to the timeouts. The SQLServer process was the one taking up the 100% CPU.
These spikes were not evident before the 99.42 days. A reboot of the SQL
Server machine was carried out and the countdown to the next 49.71 days began
:(

One thought that we have is that the problem could be caused by an open
transaction over the 49.71 day period, i.e. Starting before but ending after.
Having said this I have no explanation as to why this should cause the
problem from that point on.

At the moment it looks like we are going to go back to the scheduled reboot
before the problem hits us again; but in our 24/7 environment this is
somewhat inconvenient.

Any thoughts, ideas or suggestions are more than welcome. Not only to fix
the problem but also things to investigate.

TIA
Geoff

Relevant Pages

  • Re: Problem after 49.71 Days
    ... SQL Server and the OS keep two seperate counts of how long they have been ... The timeouts start immediately after the 49.71 days and continue until we reboot the database machine. ... We considered the possibility that the problem could be caused by clients holding connections open to the database for the 49.71 days but have taken steps to ensure that connections are not held open this long. ...
    (microsoft.public.sqlserver.server)
  • Re: TCPv4 Counters "Connetions Active" and "Connections Passive" on W2K3 IIS 6 S
    ... Active is the number of connections that are initiated by the system. ... For the SQL server some indications might be: ... values indicates that active queries are waiting for other queries. ... Also database size, queries and indexes can make huge differences. ...
    (microsoft.public.inetserver.iis)
  • Re: Strange SQL 2000 connections ! (ghost connections)
    ... I no longer like to comment on Access/JET database issues--there are simply ... Access/JET is a very challenged interface to SQL Server. ... I think that the 'ghost' connections are unrelated to your problems. ... If you find any open transactions that are more than a few minutes old, ...
    (microsoft.public.sqlserver.connect)
  • RE: ASP.NET and Access as online database?
    ... SQL Server would be the best choice unless you want to go with an open source ... would use another database. ... The max simultaneous users (connections) is much ... while online users are accessing it. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Memory issue using OleDbConnection with SQL Server
    ... > First of all, I'm not quite sure if this is the right place asking this> question, it may very well be a SQL Server issue, but I'll start here. ... > For normal operation and load, it seems to do OK, but when I do a lot of> database calls during a> short period of time, the server memory literally goes through the roof. ... > - I thought it might have something to do with the database connections> being left open in a pool, but the high memory use remains even after the> application is stopped. ...
    (microsoft.public.dotnet.framework.adonet)