Occasional "Query failed" with LAMP to MSSQL 2000 server on intensive queries

Hi,

 

Apologies in advance if this is a bit vague - I'm not expecting anyone
to be able to figure out what's going on from the following description,
I'm really looking more for advice on how to investigate further.

 

For the last few years I've been happily using FreeTDS to query an
ageing SQL Server 2000 server using PHP scripts running on a Centos 6
server. Recently, though, the amount of data I've been working with, and
the number of complex queries, has increased to the point where I
sometimes see long-running, intensive queries failing after
approximately (but this is not a firm measurement) a minute with the
following output:

 

PHP Warning:  mssql_query(): Query failed in [script_name] on line 131

Warning: mssql_query(): Query failed in [script_name] on line 131

 

Then, it seems, any further queries attempted shortly after in the same
script fail with, for example:

 

Warning: mssql_query(): Unable to set query in [script_name] on line 162

PHP Warning:  mssql_query(): Unable to set query in [script_name] on
line 162

 

This seems to occur more often if the server is particularly busy, such
that I have tried to carefully time my cronjobs not to overlap at night.
I've never seen a large query fail when I run it directly in SQL Server
Management Studio Express.

 

In some cases I can get around the issue, or at least alleviate its
frequency, by breaking large queries into smaller ones covering
different ranges of data, but this isn't ideal. I've tried changing
FreeTDS's timeout and connection timeout values, but to no avail. I also
recently increased mssql.timeout to 600 seconds in php.ini, but this
made no difference.

 

freetds.conf (minus comments) looks like this:

 

[global]

  timeout = 240

  connect timeout = 30

  text size = 64512

 

[server_name]

  host = 192.x.x.x

  port = 1433

  tds version = 7.1

  client charset = UTF-8

 

I forget the reason for setting client charset to UTF-8 but I think it
was a good one...

 

Could anyone advise me on what I can do to get more information on
what's going wrong? I couldn't find anything unusual logged on the SQL
server itself, but again I'm somewhat in the dark as to where to look.

 

Thanks in advance,

 

David