VSS Import - DB Timeout

[gregsohl]

We are in our test migration process now from VSS. We are importing from VSS into the latest Plastic v3. Things were going pretty well. About 24 hrs to get all the files/revisions imported and then it started on importing Labels. That ran for about 12 hours and then got a database timeout error. Apparently Plastic has no database error resiliency. No retries, no timeout doubling and try again. Disappointed. Hope this doesn't come up in production.

36 hours of testing and back to square 1. Still will have to do Fast Export and import into v4.1 after this too. Guess I'll need to plan a week of downtime for this migration.

Here is the error from the Server_Loader.log.txt

2012-06-12 21:40:10,850 4948 Dblair 00000000-0000-0000-0000-000000000000 JREISNER-DT1  ERROR DataConnection - Error in ExecuteReader. Query SELECT max(r.fidchangeset) as maxcs   FROM markerrealization mr, revision r  WHERE mr.fidrevision = r.iobjid	AND mr.fidmarker = 15254087 GROUP BY r.fidbranch
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.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.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
  at Codice.CM.Data.CmDataReader..ctor(CmConnection A_0, a9 A_1, y A_2)
  at y.a(CmConnection A_0, a9 A_1)
2012-06-12 21:40:10,950 4948 Dblair 00000000-0000-0000-0000-000000000000 JREISNER-DT1  ERROR Operations - OnError catching exception 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.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.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
  at Codice.CM.Data.CmDataReader..ctor(CmConnection A_0, a9 A_1, y A_2)
  at y.a(CmConnection A_0, a9 A_1)
  at y.c(CmConnection A_0, String A_1)
  at Codice.CM.Data.SqlDataQuery.GetMaxLabeledCsNumberByBranch(Int64 repId, Int64 mkId)
  at Codice.CM.Server.MarkerHandler.a(Int64 A_0, Int64 A_1, UInt64 A_2)
  at Codice.CM.Server.MarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.SecuredMarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.TransactionInterceptor.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
2012-06-12 21:40:11,010 4948	 ERROR Codice.CM.Server.ExceptionTracerSink - Dumping in-transit exception: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.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.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
  at Codice.CM.Data.CmDataReader..ctor(CmConnection A_0, a9 A_1, y A_2)
  at y.a(CmConnection A_0, a9 A_1)
  at y.c(CmConnection A_0, String A_1)
  at Codice.CM.Data.SqlDataQuery.GetMaxLabeledCsNumberByBranch(Int64 repId, Int64 mkId)
  at Codice.CM.Server.MarkerHandler.a(Int64 A_0, Int64 A_1, UInt64 A_2)
  at Codice.CM.Server.MarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.SecuredMarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.TransactionInterceptor.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.TriggerInterceptor.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
  at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Here is the error from the Import.log.txt

2012-06-12 21:40:11,048 FATAL Importer - Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Server stack trace:
  at System.Data.SqlClient.SqlConnection.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.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
  at Codice.CM.Data.CmDataReader..ctor(CmConnection A_0, a9 A_1, y A_2)
  at y.a(CmConnection A_0, a9 A_1)
  at y.c(CmConnection A_0, String A_1)
  at Codice.CM.Data.SqlDataQuery.GetMaxLabeledCsNumberByBranch(Int64 repId, Int64 mkId)
  at Codice.CM.Server.MarkerHandler.a(Int64 A_0, Int64 A_1, UInt64 A_2)
  at Codice.CM.Server.MarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.SecuredMarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.TransactionInterceptor.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at Codice.CM.Server.TriggerInterceptor.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
  at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
  at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
  at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
  at Codice.CM.Interfaces.IMarkerHandler.AddMarkerToRevisions(Int64 repId, Int64[] revisionIds, Int64 markerId, Boolean bCheckExisting, UInt64 utcTimeTicks)
  at ad.a(Int64[] A_0, String A_1, DateTime A_2, Boolean A_3)
  at ad.a(Int64[] A_0, String A_1, DateTime A_2)
  at az.a(Boolean A_0, String A_1)
  at vv.f()
  at dk.a(f A_0, Boolean A_1, Boolean A_2)

What things might we do to avoid this? Also, are there things we can do to improve the speed of the import?

We are thinking of:

1. Adding a Connection Timeout to the SQL Server connection string in db.conf. Set it to 5 min or so.
   
2. Find a way to break our import down into smaller chunks. This will be some significant manual effort or script writing though.
   

Thanks.

Greg

[gregsohl]

Correction, the initial 24 hours was 48 hours. So we were 60 hours in before the error. I forgot another day went by. Sigh...

[gregsohl]

Can wildcards be used on the VSS Project name using the Import tool?

Ex. MyProject\BaseName.Level1.*

[manu]

Hello Greg,

As you said, I would try adding a big Connection Timeout into the db.conf Connection String tag.

Let see if it helps.

[psantosl]

Since there's no good way to migrate from VSS to Git format, we'll need to come back with our own importer tool... ouch!

[gregsohl]

psantolsl,

I didn't understand what you meant by that. Can you explain further? I'm using your importer tool now.

Right now I've written a powershell script to migrate our VSS to Plastic 3 in small chunks (by sub project) calling climporter.exe directly. Minimal tests have been positive in getting into v3, however, as Jim posted here () we are having some trouble in that scenario with the subsequent fast-import to Plastic v4.

Greg

[psantosl]

I mean having to import to 3.0 prior to 4.0. I bet importing directly to 4.0 would be better, right?

[gregsohl]

It would certainly be better not to have to do a 3 step import (1: import to v3, 2: export to fast-export, 3: fast-import to v4).

As far as direct import to v4 from VSS instead of import to v3 from VSS, I don't know if there would be benefit there or not. But if you made portions of your code open source, (e.g. the import program) I'd be glad to give you some feedback on it.