SQL connection timeout after 100 minutes

bgraves · April 12, 2011 1:02PM

I'm trying to do a data compare on a single table between 2 servers. This runs every hour and works most of the time. However, every couple of days I consistently get the following error (after 6002 seconds) until I restart the destination SQL server:

Exception: System.Exception: Unable to setup databases for replication. Internal error:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at j.j(SqlConnection )
at j.a()
at RedGate.SQLCompare.Engine.Database.RegisterForDataCompare(ConnectionProperties connectionProperties, Options options)
at RedGate.SQLCompare.Engine.Database.RegisterForDataCompare(ConnectionProperties connectionProperties)

Any idea why this could be happening?

peter.peart · April 14, 2011 3:41PM

Hi there,

Thanks for your post. Generally, when we have seen this before it relates to a problem with connectivity on the host machine which would explain why a reboot resolves the issue.

Out of curiosity, how large is the data set in those tables?

Pete

bgraves · April 14, 2011 4:47PM

It's just one table. It is usually around 20,000 rows (it gets cleared out every hour after the data is moved to the destination server). The destination server isn't rebooted...SQL server is just stopped and restarted.

What connectivity problem are you referring to?

peter.peart · April 15, 2011 8:53AM

Hi there,

Connectivity problem is this:

System.Data.SqlClient.SqlException &#40;0x80131904&#41;: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

See also, which refers to it either being a timeout on the query or network connectivity:

http://stackoverflow.com/questions/810673/connection-problems-with-sql-server-in-asp-net-applications-using-out-of-process

You can add a connection timeout value in the server name that you are connecting to within the batch file, i.e.:

sqldatacompare /s1:"local;connection timeout=value in seconds"

It could be very well the the timeout if the connection is particularly slow between these machines. Maybe try increasing the timeout to 20 minutes to see if that helps.

Pete

bgraves · April 18, 2011 12:43PM

Thanks, but it's timing out after 100 minutes (6002-6003 seconds) just for one table so will changing the timeout to 20 minutes help? Since we're currently not setting timeout for connection or command, what are the default values that redgate is using?

James B · April 19, 2011 8:47AM

I believe the commandtimeout defaults to 6000, which would explain the length it runs for before failing. I don't think there's any way to override that though.

How long does the process normally take? Is it close to the 100 minutes usually, or does it normally complete in, say, 10 minutes?

When it fails do you see any messages in the SQL Server error log at all?

bgraves · April 19, 2011 2:16PM

How long is the connection timeout by default? Would setting this 20 minutes help at all?

The process usually takes 6-25 seconds unless we get these 100 minute connection timeouts. It's always happening on a single table. The only messages in the SQL Server error log is the "Log was backed up" message that occurs every hour on the hour. Otherwise, I'm getting these timeout exception consistently throughout the night (no select is taking place...so it looks like the connection is timing out!) until it starts working again early in the morning:

Exception: System.Exception: Unable to setup databases for replication. Internal error:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at j.j(SqlConnection )
at j.a()
at RedGate.SQLCompare.Engine.Database.RegisterForDataCompare(ConnectionProperties connectionProperties, Options options)
at RedGate.SQLCompare.Engine.Database.RegisterForDataCompare(ConnectionProperties connectionProperties)

James B · April 19, 2011 2:23PM

I believe the default connection timeout is 30 seconds for a sql connection - I'm not sure if we alter this in our software. But you can specify your own value in the connection as Peter suggested.
Given that it fails after the 6000 period, it seems more like it's the command itself that's timing out for some reason; possibly because it's being blocked by some other process. This would make sense if restarting the SQL Service then gets it going again. Of course, working out what that is is the tricky part. You could try running sp_who2 to see if that lists other active processes against the DB in question? That may help...

bgraves · April 19, 2011 2:40PM

I'm confused now. First, it was a connectivity issue where the connection was timing out (as shown in the stack trace). Now it's a command timeout? Wouldn't the exception get thown on a SQLcommand instead of a SQLconnection if this was the case?

Yes, I have used the sp_who2 and no sysprocesses are getting blocked when this is happening. However, there is no redgate SYSPROCESS to BLOCK anyway (unusual since there is always one running when everything is working correctly) during the period before the timeout eventually occurs. Again there is no select command that can get timed out since there is no redgate sysprocess running...so what does this exception mean anyway?

Anyway, it sounds like changing the connection timeout to 20 minutes will have no effect since the timeout occurs after 100 minutes. Correct?

James B · April 19, 2011 3:05PM

I don't know for sure that it's a command timeout - I'm just thinking it's possible as the timeout that we give that happens to match the timescale that your process fails after.
I don't see anywhere that we set the connection timeout, so I have to assume it uses the default value that .NET uses with the SQLConnection - for instance, try connecting in the GUI to a non-existent server. It failed after a couple of minutes for me, rather than the 100 minutes you see which suggests that the it may not be the initial connection that's timing out.
But then as you point out, if you don't see any Redgate process that would suggest more that it is indeed the connection that's timing out rather than the command execution. So it's a tricky one to pinpoint.

If it normally takes just a few seconds to run, then I'm not sure whether increasing the timeout will help - even if you set it to say, 2 hours, I'm guessing it would sit for that long before failing; but working out why the connection can't be established is tricky- it may be worth trying a longer timeout as a simple test (maybe 7200?), but if that still doesn't help, I'm not sure what the next step would be....

bgraves · April 19, 2011 3:14PM

Which redgate connection is timing out in this case (I gave you the stack trace). Also, what is the Redgate RegisterForDataCompare function supposed to be doing? That is where the timeout is occurring. I can't troubleshoot this function since there's no source for it. I really need your help to figure out why the exception is getting thrown in this function. I'm guessing the connection timeout is set to 100 minutes since no command is running...but I really need to verify. Please help me verify this. Thank you.

James B · April 19, 2011 3:19PM

The RegisterForDataCompare is when the connection is established and the schema read and so on.
So, if it's that part that's failing, then it won't be the command, it will be the connection timeout.
There's not much that can be done in the application to get around that- if increasing the timeout doesn't help then for whatever reason, the server isn't accepting the connection and never will.

Once it is timing out, can you connect using SSMS from the same place you run Data Compare?

bgraves · April 19, 2011 3:28PM

Okay, so we've established that it's a connection issue since the exception is always thrown in the RegisterForDataCompare function, correct? Yes, I can connect fine in SSMS when this problem happens. Perhaps the redgate connections are NOT being freed up (do I need to call a redgate function to do this) and no more can be established until I restart SQL server?

James B · April 19, 2011 3:31PM

There shouldn't be any command needed to free up the data compare connection - once the program closes, the connections should be closed. It may be worth checking in task manager though to see if in fact it's still running in the background for some reason? Also, what version are you on? 9, or still using a v8?

bgraves · April 19, 2011 3:36PM

My version is 6.2.2.14 from April 2010. Was there a bug in this version?

James B · April 19, 2011 3:43PM

I can see an issue with connections not being closed that was fixed in 6.0, so that shouldn't affect you. I can also see a similar issue occurred in version 8 at one point but was then fixed.

Although I can't spot anything specifically affecting your version, it's probably worth you upgrading to the latest version to see if the problem persists, just in case there was a problem with connections being held open in your version.

James B · April 19, 2011 3:45PM

Thanks for your post.

Unfortunately it's not possible to sync just certain aspects of an object. You can ignore Indexes from beign compared and synced (the GUI has a tickbox option for this, and I'm sure there's an equivalent option in the SDK) but asides from that the sync operates on the whole table object.

bgraves · April 22, 2011 5:44PM

I upgraded to the latest version. So far (knock on wood) I haven't experienced any timeout issue. However, doing a data compare on 30 tables used to take about 10 minutes on the average...with these new libraries it takes close to 20 minutes on the average. This happened immediately after I switched to the new libraries. Hopefully, the extra time makes it more reliable...but at double the time I'm not sure how feasible it is to use. Is there a reason that it takes twice as long between v6 and v9? Is there any way I can speed it up using a redgate library function?

SQL connection timeout after 100 minutes

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk