[sims] SIMS Discover 2.154 hanging transfer

[Jon-Dukes]

Hi Guys

 

After recently updating to SIMS 7.155 I have updated SIMS Discover to 7.154. However now my data transfers are hanging for a few hours before eventually failing. The log file says the following:

 

There was an error transferring the data between the SIMS and Discover databases!

&

Unable to transfer data between SIMS and Discover after 5 attempt(s) - waiting for next request!

 

Anybody else experienced/experiencing this?

 

Any advice would be greatly appreciated.

 

Thanks in advance.

[tombry]

pretty sure the last time I had this issue I had the SA password incorrect.

[Rawns]

What else is in the log file? Usually there is a reason listed for the failure (timeout perido expired or issues with specific references to the tables in the database).

 

I'd firstly extend the Discover time-out period. Navigate to C:\Program Files (x86)\SIMS\Discover Services. Take a copy of Discover.Warehouse.Service.exe.config and name it Discover.Warehouse.Service.exe.config.old. Then open Discover.Warehouse.Service.exe.config in notepad. There should be a section titled like below:

 

 

Underneath add the following:

 

 

This will extend the timeout period to 10 minutes.

 

It's also worth applying the SIMS Reindex patch to the SIMS database. I've had these issues in schools before and the reindex patch and a server reboot seems to straighten the transfer service out in most cases.

Edited January 31, 2014 by Rawns

[Jon-Dukes]

Do you mean when originally installing Discover the SQL Authentication password was incorrect? Because when I installed I used the Trusted Authentication option. Or is there somewhere else when I provide the SA password?

 

Sorry if this makes no sense, I'm new to this.

[Rawns]

Any errors in the log file about the NETWORK SERVICE not having access to transfer data?

[Jon-Dukes]

I am getting the following in the log file over and over again.

 

 

Increasing DiscoverBulkCopyTimeout

Timeout has not been increased. Exception is not a SQL timeout exception.

Increasing SimsCommandTimeout

Timeout has not been increased. Exception is not a SQL timeout exception.

There was an error rolling back the transaction within Discover database! at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()

at System.Data.SqlClient.TdsParserStateObject.ReadByte()

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)

at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)

at System.Data.SqlClient.SqlInternalTransaction.Rollback()

at System.Data.SqlClient.SqlTransaction.Rollback()

at Discover.Warehouse.TransferAgent.Transferor.GetStudentData(Int32 partitionID, Int32 partitionOrder, IDbConnection simsConnection, IDbConnection discoverConnection, ITransferConfiguration transferConfig, List`1 metrics)

There was an error transferring the data between the SIMS and Discover databases! at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()

at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)

at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

at Discover.Warehouse.TransferAgent.Transferor.ExecuteDiscoverBulkCopy(Int32 partitionID, IList`1 discoverTables, IDataReader simsDataReader, IDbConnection discoverConnection, IDbTransaction discoverTransaction, List`1 metrics)

at Discover.Warehouse.TransferAgent.Transferor.ExecuteSimsLoad(ITransferConfiguration transferConfig, IDbTransaction discoverTransaction, IDbConnection discoverConnection, IDbConnection simsConnection, List`1 metrics)

at Discover.Warehouse.TransferAgent.Transferor.GetStudentData(Int32 partitionID, Int32 partitionOrder, IDbConnection simsConnection, IDbConnection discoverConnection, ITransferConfiguration transferConfig, List`1 metrics)

at Discover.Warehouse.TransferAgent.Transferor.TryExecute(Int32 partitionID, Int32 partitionOrder, DateTime partitionTime)

Unable to transfer data between SIMS and Discover after 5 attempt(s) - waiting for next request!

[Rawns]

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

There's your issue. The transfer is timing out, usually it's because of the database and/or server performance. Extend the timeout transfer period as I posted above, get the SIMS reindex patch applied and bounce your box overnight (ideally before the time the transfer scheduled tasks run). You should then find the 1am overnight transfer task works successfully.

 

Also worth just checking the size of your Discover.ldf database file. I've seen some schools where it's grown to a stupid size and releasing the space if it's in simple recovery mode helps.

Edited January 31, 2014 by Rawns

[vote-for-pedro]

Did you have any success in fixing this? I have a similar Duplicate primary key issue

 

There was an error transferring the data between the SIMS and Discover databases! at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)

at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)

at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)

at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)

at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

at Discover.Warehouse.TransferAgent.Transferor.ExecuteDiscoverBulkCopy(Int32 partitionID, IList`1 discoverTables, IDataReader simsDataReader, IDbConnection discoverConnection, IDbTransaction discoverTransaction, List`1 metrics)

at Discover.Warehouse.TransferAgent.Transferor.ExecuteSimsLoad(ITransferConfiguration transferConfig, IDbTransaction discoverTransaction, IDbConnection discoverConnection, IDbConnection simsConnection, List`1 metrics)

at Discover.Warehouse.TransferAgent.Transferor.GetStudentData(Int32 partitionID, Int32 partitionOrder, IDbConnection simsConnection, IDbConnection discoverConnection, ITransferConfiguration transferConfig, List`1 metrics, Int32 allowGenerate)

at Discover.Warehouse.TransferAgent.Transferor.TryExecute(Int32 partitionID, Int32 partitionOrder, DateTime partitionTime, Int32 allowGenerate)

Unable to transfer data between SIMS and Discover after 5 attempt(s) - waiting for next request!

[Cache]

Your error message is slightly different:

 

There was an error transferring the data between the SIMS and Discover databases!

 

Are both SIMS and Discover on the same release (Spring/Summer/Autumn) - last time we got that error was because SIMS was on the Summer release but Discover was still on Spring.

[vote-for-pedro]

Alas they are the correct versions. Backup of database been sent to capita for them to have a look at. Will let you know.