Timeout Expired

kevine323kevine323 Posts: 58
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!
Kevin Eckart
Database Administrator
USA Truck, Inc
[email protected]
http://kevine323.blogspot.com/

Comments

  • Any ideas?
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • Does SQL Source Control give you any other error information when it throws the error, or is it just 'Timeout expired'?
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • 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 )
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • Any ideas?
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • 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.
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • 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 ;
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • 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.
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • 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.
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • 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?
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • 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.
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • Does this persist if you unlink and relink the database?
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • yes
    Kevin Eckart
    Database Administrator
    USA Truck, Inc
    [email protected]
    http://kevine323.blogspot.com/
  • 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!
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • 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
  • 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.
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • 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.
  • 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
    Andy Campbell Smith

    Red Gate Technical Support Engineer
  • PDinCAPDinCA Posts: 642 Silver 1
    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;
    
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.