Could do with some DB specific help.

I’ve got an RHEL/Tomcat server using a JDBC connector to connect to a MS SQL JDBC driver and in turn three databases hosted on SQL Server 2005.

Every night after a period of inactivity, the processes are being terminated or dropped between the two servers and the JDBC connector on the RHEL server has to re-initiate the connection.

The JDBC connector string isn’t specifying a timeout value to the MS JDBC driver. The connection should stay up. The connection is being terminated or dropped during the night.

Is there any decent logging I can turn on with either the MS SQL 2005 Server or the MS SQL JDBC driver that would track changes to the process ID state?

The connection remains idle for normally just over 12 hours, so I’m unsure if I have a separate timeout value configured somewhere or if a maintenance job is killing it (can’t see one).

SQL Server doesn’t terminate/timeout connections, it’s done client side – so possibly a setting to add to the connector, maybe it defaults to something long but under 12 hours? Maybe it needs to be explicitly set to 0 or something to never timeout.

Is the connection over a ‘good’ link (i.e. LAN or MetroLAN on dark fibre.. not MPLS cloud WAN or the like)? Is it environmental rather than your config..

A maintenance job that switches the DB to single user and back to drop all open connections is possible (I do it on a db I ship across the wan and restore, to kick out user sessions nightly, then flick back multi user)

SQL Profiler is worth a tickle for tracking the server side

(I am not a real DBA, an involuntary DBA :-))

Thanks brassneck.

Tried SQL profiler, but it is showing no entries at all for these JDBC connections, despite being run for the time when the connection is re-established.

Both servers are physically cabled into the same switch and are both on the same VLAN, so I don’t suspect the network at this stage.

The maintenance jobs don’t place any single user mode restrictions and only the connections from this server drop overnight. Problem is that this server is the only one using the MS SQL JDBC driver. Others unaffected are using JSQL Connect.

The developers of the application sending the JDBC connection string from the RHEL server say there is no explicit timeout value and they don’t see this issue elsewhere.

Sure it’s not a firewall/network switch setting which is terminating the connections ?

You could run a continuous ping overnight to see if it is a general TCP/IP issue.

No firewall between the servers. No switch configuration that can impact like this.

I’ve enabled FINEST logging for the MS SQL JDBC driver to see if that turns something up.

The developers of the application sending the JDBC connection string from the RHEL server say there is no explicit timeout value and they don’t see this issue elsewhere.

That’s what our devs over the water said too 🙂 – turned out to be an issue with their settings 😀 .. not an identical situation though, this was more select * from * on a 30Gb of data and act surprised when it takes a while over a 40Mbps link.

Not a version issue? Tried the latest JDBC driver?

Check out this link just in case you’re not tracking the right events. Doubt it, but it seems odd you’re not seeing it logon/logoff at the very least. Maybe it only does that at app startup/shutdown and just holds a socket open?

I might be being dim, but any reason not to use the JSQL Connect with TomCat? Support from the devs I guess?

I’ve enabled FINEST logging for the MS SQL JDBC driver to see if that turns something up.

Big log file I’m guessin’ 🙂

33tango – Member

You could run a continuous ping overnight to see if it is a general TCP/IP issue

I would expect to see other external connections dropping. I have 57 external connections open at the moment. Only the JDBC connections to one server drop. Issue is the other connections are not using the MS SQL JDBC driver so it is a possibility other than the RHEL server.

No switch configuration that can impact like this.

Only thing I could think of from what you’ve described might be a duplex mismatch or hard coded port with an Auto adaptor or vice versa.. but it seems unlikely to be network across the same switch and VLAN or you’d be having bigger problems on the switch.

Only thing I could think of from what you’ve described might be a duplex mismatch or hard coded port with an Auto adaptor or vice versa.. but it seems unlikely to be network across the same switch and VLAN or you’d be having bigger problems on the switch.

Yeah, everything is running happily. All the servers have gigabit nics, all are set to auto/auto and all interfaces are clean as a whistle. The RHEL server also has links to other servers across the same switch with no other connections experiencing issue. Only issue seems to be JDBC connectivity between this RHEL server and the MS SQL JDBC driver and the MS SQL server behind it.

Feeback from the devs of the RHEL application is that there is no timeout and that the application doesn’t even monitor the connection. Basicially it’s only when it tries to shuff data across, a SQL exception is returned as there is no active process, the app panics and re-establishes the JDBC connection string and all is well until the next time it happens.

Also, can’t change from JDBC as it will involve a whole dev lifecycle and change of product.

I can update the MS SQL JDBC driver, but would like to get more evidence that it is the issue.

I can update the MS SQL JDBC driver, but would like to get more evidence that it is the issue.

Then I think you’ve covered it with updating the logging detail. Be interested to hear if it shows anything.

Out of interest, why is re establishing the connection an issue, is it loading the frontend server too highly by happening a lot?

Last thing I could think of network wise was NIC teaming – ifenslave on RHEL, or OEM drivers on RHEL and Windows .. can sometimes cause more trouble than it’s worth if running in a load balance mode rather than just failover, but again I’m sure it’d be more obvious

The JDBC connector string isn’t specifying a timeout value

Feeback from the devs of the RHEL application is that there is no timeout

So does that mean there is no timeout configured therefore it falls back on whatever the client/OS default is (which is….?), or do they mean it is specifically set to never timeout?

Does the drop happen at the same time every day, or around the same time, ie: do you think it is dropping at time X, or after period of Y, if that makes sense?

You could try strace’ing the process on the RHEL (5/6 ?) box to monitor there and see if you can spot what’s happening, could also be worth running tcpdump to capture the comms over the period when it normally drops to see if there is anything going on there.

I know you said no FW between them, but are the two servers literally joined by a switch, or is there any kind of load balancer/FW/Content switch etc in between? Any local iptables or FW running on either server?

As a test could you configure an automated dummy connection on a regular interval to keep the connection alive? this might help prove if it is an idle timeout problem or not.

Is any DB maintenance set to run overnight? Could be setting the dB to single user to perform a backup. From memory that will forcibly drop all other connections. Try testing with a dummy dB that your sure doesn’t have any scheduled jobs.

I was just going to mention what jeffl said 🙂

Its overnight, yeah? Check what happens when it runs the backups.

Ran this past my brother and he says :

Sql server has a default ten minute timeout on all incoming sessions unless explicitly overridden. Profiler will likely give sine help on this issue but only if you’re tracing the right elements of the right events. I’d recommend trying this method again expanding what you’re logging but filtering on the one server’s hostname to reduce noise just a little. Also check some logs for a buffer overflow error in case a prior job or process is erroring without making much noise about it and subsequently closing the connection every time.

Frankly sounds like gibberish from all of you

Are you sure the connection is being dropped and the application is not doing something odd – like trying to initiate a new connection for a new day?

You can query the sys.dm_exec_sessions DMV to view what’s connected to SQL Server and when its connection was established.

/*DBA hat on*/
Sounds like an application problem, the servers fine…

/* Consultant hat on*/
SQL Server 2005 is out of mainstream support, have you considered upgrading to SQL Server 2014?

😀

Have you tried switching it off and on again? 🙂

+1

I don’t deal with Microsoft server side stuff but on other databases then I’d be looking into the scheduled jobs overnight, specifically backups.

Feeback from the devs of the RHEL application is that there is no timeout and that the application doesn’t even monitor the connection. Basicially it’s only when it tries to shuff data across, a SQL exception is returned as there is no active process, the app panics and re-establishes the JDBC connection string and all is well until the next time it happens.

I’d be questioning the devs on why the application doesn’t open and close down the connection cleanly when it’s done with it. Assuming a connection is there is very poor form.

MSSQL is a real DB server (Oracle/DB2 DBAs will of course disagree!) – it doesn’t need to go single user to back up. VSS will probably allow a consistent backup of a db from a filesystem backup whilst open, but I wouldn’t bet my job on that.
My script is specifically for restoring a shipped DB from another site onto a different server, as it won’t restore with active sessions in place. It’s a bit of a kludge but it works well enough.

Sql server has a default ten minute timeout on all incoming sessions unless explicitly overridden.

Pretty sure this isn’t true. The limit most people think this 10 minute rule applies to is actually a server setting for OUTGOING queries to other servers (http://msdn.microsoft.com/en-us/library/ms189040.aspx) – admittedly it’s terribly named.

/*DBA hat on*/
Sounds like an application problem, the servers fine…

/* Consultant hat on*/
SQL Server 2005 is out of mainstream support, have you considered upgrading to SQL Server 2014?

<Applauds> 😀