Timeout Expired

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

Timeout Expired

Postby kevine323 » Wed Aug 22, 2012 5:15 pm

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
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Any ideas?

Postby kevine323 » Fri Sep 07, 2012 5:07 pm

Any ideas?
Kevin Eckart
Database Administrator
USA Truck, Inc
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/
kevine323
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby andy.campbell.smith » Mon Sep 10, 2012 4:17 pm

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
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby kevine323 » Mon Sep 10, 2012 4:36 pm

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
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby kevine323 » Mon Sep 17, 2012 4:14 pm

Any ideas?
Kevin Eckart
Database Administrator
USA Truck, Inc
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/
kevine323
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby andy.campbell.smith » Wed Sep 19, 2012 3:24 pm

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
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby kevine323 » Wed Sep 19, 2012 4:15 pm

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/
kevine323
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby andy.campbell.smith » Wed Sep 19, 2012 4:26 pm

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
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby kevine323 » Wed Sep 19, 2012 5:10 pm

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/
kevine323
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby andy.campbell.smith » Fri Sep 21, 2012 10:22 am

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
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby kevine323 » Fri Sep 21, 2012 2:07 pm

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/
kevine323
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby andy.campbell.smith » Fri Sep 21, 2012 2:17 pm

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

Red Gate Technical Support Engineer
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby kevine323 » Fri Sep 21, 2012 2:30 pm

yes
Kevin Eckart
Database Administrator
USA Truck, Inc
Kevin.Eckart@usa-truck.com
http://kevine323.blogspot.com/
kevine323
 
Posts: 39
Joined: Thu Jul 27, 2006 10:23 pm

Postby andy.campbell.smith » Fri Oct 05, 2012 12:51 pm

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
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Is it still suggested to downgrade to 3.0 to fix timeout?

Postby Bellisio » Wed Oct 17, 2012 1:52 pm

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
Bellisio
 
Posts: 19
Joined: Wed Oct 29, 2008 8:09 pm
Location: State of CT - DDS

Next

Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 0 guests