Timeout Expired

kevine323 · August 22, 2012 12:15PM

I continue to get a Timeout Expired error message in the Commit Changes table on very large databases that are running on slow test systems. Is there a way to configure the timeout?

Thanks!

kevine323 · September 7, 2012 12:07PM

Any ideas?

Andy C S · September 10, 2012 11:17AM

Does SQL Source Control give you any other error information when it throws the error, or is it just 'Timeout expired'?

kevine323 · September 10, 2012 11:36AM

System.Data.SqlClient.SqlException: 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(TdsParserStateObject stateObj)
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 RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.#Qadc(IDictionary`2 , SqlCommand )
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.#Emb(ICollection`1 , Nullable`1 )
at RedGate.SQLSourceControl.Engine.DatabasePolling.DatabasePolling.ServerParsers.DefaultTraceParser.GetAllEntries(String databaseName)
at #mlhb.#ny2b.#xFV()
at #mlhb.#ny2b.#aQU(ICollection`1 #vfX, DatabaseObjectIdDictionary`1 #xfX, SourceControlOperation #oTc, Differences #Ncsc)
at #mlhb.#qy2b.#L8c(SourceControlOperation )
at #mlhb.#ry2b.#zYM(IDifferenceSelector #xrWb, ICancellableOperationStatus #KHc, IReadOnlySourceControlServerCallBacks #VHc, ToCommitChangeSet& #Ocsc)
at #GWeb.#VXM.#mYM[#NrPb](Func`2 , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector , Action )
at #GWeb.#VXM.#jYM(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector )
at #GWeb.#4Ec.#7Jc(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector )
at #GWeb.#4Ec.#y27.#QB7b(ICancellableOperationStatus )
at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.#u36.#k5f()
at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationBase.InvokeWithTracker(String featureUsageKey, Action action)
at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke()
at #eEc.#Qlg.Invoke()
at #JLc.#PLc.#Jfb.#EJf()
at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.Do(Action , Predicate`1 , Boolean )
at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.DoWithObviousExceptionsRethrowAll(Action action)
at RedGate.SQLSourceControl.CommonUI.Forms.ErrorDialog.DoWithObviousExceptionsRethrowAll(Action action)
at #JLc.#PLc.#CTc(ICancellableOperation`1 , Object )

kevine323 · September 17, 2012 11:14AM

Any ideas?

Andy C S · September 19, 2012 10:24AM

Sorry about the delay - I've been trying to find out if there's any undocumented way to configure this timeout in the config files. Consensus is that it's just a regular SQL query timeout, so not a SQL Source Control thing - you can try editing the query timeout value in SSMS and see if that helps with SQL Source Control, but of course that'll have knock-on effects for other queries as well.

kevine323 · September 19, 2012 11:15AM

The query timeout was already set to unlimited.

I ran a trace on the server that was getting the timeout in source control and the following statement continues to run over and over even after the application return the timeout statement.

-- Copyright © 2009 â€“ 2012 Red Gate Software Ltd.
SET XACT_ABORT ON ;
SET LOCK_TIMEOUT 100 ;
BEGIN TRAN ;

IF OBJECT_ID(N'tempdb..#RG_NewSysObjects', N'U') IS NOT NULL
DROP TABLE #RG_NewSysObjects ;

SELECT *
INTO #RG_NewSysObjects
FROM [DYNAMICS].sys.objects ;

SELECT ls.name AS prevName ,
curr.name AS name ,
ls.object_id AS prevId ,
curr.object_id AS id ,
ls.SchemaName AS prevSchemaName ,
sysSchemas.name AS schemaName ,
ls.type AS prevType ,
curr.type AS type
FROM #RG_LastSysObjects AS ls
FULL OUTER JOIN #RG_NewSysObjects AS curr ON ls.object_id = curr.object_id
LEFT JOIN [DYNAMICS].sys.schemas AS sysSchemas ON sysSchemas.schema_id = curr.schema_id
WHERE ( curr.type IS NULL
OR curr.type NOT IN ( 'C', 'D', 'F', 'IT', 'PK', 'S', 'TA', 'TR', 'TT', 'UQ' )
OR ( curr.type = 'D'
AND curr.parent_object_id = 0
)
)
AND ( ls.modify_date <> curr.modify_date
OR ls.object_id IS NULL
OR curr.object_id IS NULL
OR ls.name <> curr.name --TODO schema/type changes
)
ORDER BY curr.modify_date ;

IF OBJECT_ID(N'tempdb..#RG_LastSysObjects', N'U') IS NOT NULL
DROP TABLE #RG_LastSysObjects ;

SELECT sysObjects.object_id ,
sysSchemas.name as SchemaName ,
sysObjects.name ,
sysObjects.type ,
sysObjects.modify_date
INTO #RG_LastSysObjects
FROM #RG_NewSysObjects AS sysObjects
LEFT JOIN [DYNAMICS].sys.schemas AS sysSchemas ON sysSchemas.schema_id = sysObjects.schema_id
WHERE sysObjects.type NOT IN ( 'C', 'D', 'F', 'IT', 'PK', 'S', 'TA', 'TR', 'TT', 'UQ' )
OR ( sysObjects.type = 'D'
AND sysObjects.parent_object_id = 0
) ;

COMMIT ;

Andy C S · September 19, 2012 11:26AM

The statement you see running over and over is SQL Source Control polling the database for changes - if you want, you can turn polling off, but then you'll have to manually check for updates. See this thread for more details about that:

http://www.red-gate.com/MessageBoard/vi ... ht=polling

There's definitely nothing configurable to do with timeouts in SQL Source Control, so I guess if it's not governed by the server query timeout limit it must be hardcoded? Roughly how long does it take before you get a timeout on this server? The default ought to be something like ten minutes.

kevine323 · September 19, 2012 12:10PM

I just updated to the latest minor release and the timeout issues seem to have gone away. But now I seem to have a different issue with the same table showing in source control over and over even after I just commited it.

Andy C S · September 21, 2012 5:22AM

Well, it's good to hear your timeout issues are gone. Is there anything non-standard about the problem table? What does SQL Source Control say is the change to commit?

kevine323 · September 21, 2012 9:07AM

Source control seems to be having problems commiting bindings:

EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[dbo].[USA_DYNTableDefWork].[rSysTablePhysicalName]'
GO
EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[dbo].[USA_DYNTableDefWork].[rSysFieldName]'
GO
EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[dbo].[USA_DYNTableDefWork].[rSysField1]'
GO

This is the issue on each of the 6 tables that will not commit.

Andy C S · September 21, 2012 9:17AM

Does this persist if you unlink and relink the database?

kevine323 · September 21, 2012 9:30AM

yes

Andy C S · October 5, 2012 7:51AM

Just following up on this - I've spoken to the developers about this issue, and they've identified the cause and are working to fix it. Their suggested workaround is to downgrade to SQL Source Control 3.0 - the installer for that is here:

ftp://support.red-gate.com/patches/SQLS ... 3.4214.exe

We're working on getting a fixed version out as soon as possible. Sorry about the inconvenience!

Bellisio · October 17, 2012 8:52AM

Thanks for this topic, I have been going crazy with one db that I cannot commit changes due to timeout issue. Have tried unlink-relink.

Just checking - is this downgrade to 3.0 still the solution?

TIA

Andy C S · October 17, 2012 10:52AM

Yep, we're still working on a fixed version - it should be out very soon. If you're unwilling/unable to downgrade you can try deleting all records from the RG_AllObjects table, but you'll have to keep doing that periodically as it'll fill up again. I'll post here when the new version's out.

Bellisio · October 17, 2012 10:57AM

Thanks very much for the reply Andy. I did downgrade, and was able to check in, so now I'm happy as a clam again. Will watch for the fixed version.

Andy C S · October 25, 2012 12:42PM

We've just released the new version of SQL Source Control - can you let me know if that fixes any of the issues you were seeing? It's available with the Check for Updates functionality within the tool or from our ftp site here: ftp://support.red-gate.com/patches/SQLS ... 0.4829.exe

PDinCA · October 26, 2012 3:00PM

Withdrawn! Jury's out on the patch - server ground to a halt. Rebooting...

UPDATE - ERROR EXCEPTIONS BEING THROW

Event Type:	Error
Event Source:	SQL Source Control 3
Event Category:	None
Event ID:	32
Date:		10/26/2012
Time:		2:13:08 PM
User:		N/A
Computer:	XCOUTSIDE1
Description:
ng.ErrorReporterBase
#6:Not reporting error
RedGate.Shared.Utils.OperationCancelledException: Exception of type 'RedGate.Shared.Utils.OperationCancelledException' was thrown.
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationStatusConvenienceOverloadsBase.CancelIfCancelled&#40;&#41;
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke&#40;&#41;
   at #eEc.#Qlg.Invoke&#40;&#41;
   at #JLc.#PLc.#Jfb.#EJf&#40;&#41;
   at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.Do&#40;Action , Predicate`1 , Boolean &#41;

Followed 13 seconds later when changing to another DB

Event Type:	Error
Event Source:	SQL Source Control 3
Event Category:	None
Event ID:	32
Date:		10/26/2012
Time:		2:13:21 PM
User:		N/A
Computer:	XCOUTSIDE1
Description:
ng.ErrorReporterBase
ConfigurableThreadPool thread:Not reporting error
RedGate.Shared.Utils.OperationCancelledException: Exception of type 'RedGate.Shared.Utils.OperationCancelledException' was thrown.
   at RedGate.SQLSourceControl.Engine.Diff.Memoization.ReattachableMemoizer.#uz2b&#40;String , IDatabaseConnection , #1heb , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , Action &#41;
   at #qlhb.#0heb.#wieb&#40;#L3c , #ZZ7 , #A57 , IDatabasePollingManager , IObjectExplorerStateService , #82Vc , #32Vc , #4Ec , #j3l , Options , #quBb , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , Action &#41;
   at #GWeb.#VXM.#mYM&#91;#NrPb&#93;&#40;Func`2 , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector , Action &#41;
   at #GWeb.#VXM.#jYM&#40;ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector &#41;
   at #GWeb.#4Ec.#7Jc&#40;ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks , IDifferenceSelector &#41;
   at #GWeb.#4Ec.#y2.#QB7b&#40;ICancellableOperationStatus &#41;
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.#u3.#k5f&#40;&#41;
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationBase.InvokeWithTracker&#40;String featureUsageKey, Action action&#41;
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke&#40;&#41;
   at #GWeb.#4Ec.#eqzc&#40;ICancellableOperation`1 &#41;
   at #GWeb.#4Ec.#OUe.#hqzc&#40;&#41;
   at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.Do&#40;Action , Predicate`1 , Boolean &#41;

Timeout Expired

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk