Timeout Expired

[kevine323]

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
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[kevine323]

Any ideas?
_________________
Kevin Eckart
Database Administrator
USA Truck, Inc
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[andy.campbell.smith]

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

[kevine323]

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
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[kevine323]

Any ideas?
_________________
Kevin Eckart
Database Administrator
USA Truck, Inc
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[andy.campbell.smith]

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

[kevine323]

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
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[andy.campbell.smith]

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/viewtopic.php?t=15612&highlight=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

[kevine323]

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
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[andy.campbell.smith]

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

[kevine323]

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
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[andy.campbell.smith]

Does this persist if you unlink and relink the database?
_________________
Andy Campbell Smith

Red Gate Technical Support Engineer

[kevine323]

yes
_________________
Kevin Eckart
Database Administrator
USA Truck, Inc
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/

[andy.campbell.smith]

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/SQLSourceControl/16Jul2012/SQLSourceControl_3.0.13.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

[Bellisio]

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