FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

2015-03-19 15:47 GMT+00:00 David Horman <david.horman AT odf-limited.com>:
> 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
>

Which FreeTDS version are you using?

Configuration looks fine. Just to try if there is any timeout problem
(could be due even to some firewall) try the WAITFOR statement, see
https://msdn.microsoft.com/en-us/library/ms187331.aspx. This statement
just wait so beside the connection does not occupy the server/network.

Another problem could be lock contention. If there are too much
locking server timeout automatically to prevent deadlock. I don't
remember but there should be a setting for this timeout (on the
database server).

What mssql_get_last_message say? As database server is already busy
I'd put some extra logging on the web server to see which kind of
errors happen.

Regards,
  Frediano