question

CPouyioukka-3808 avatar image
0 Votes"
CPouyioukka-3808 asked JeffreyWilliams-3310 answered

Error with MS SQL Server when importing large flat files.

Software: MS SQL Server 2019 Enterprise Edition.
Windows 10 Pro, 20H2
Problem:
I am trying to import large flat files into my database. Files are normalised and can be read by the import wizard. I successfully imported a file of relatively small size (90MB) of the many that I have but the rest result in the following error. Largest file has a size of 72GB, it can be read successfully by the import wizard prior to trying to complete the import steps.

===================================
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft.SqlServer.Import.Wizard)


Program Location:
at Microsoft.SqlServer.Import.Wizard.InsertData.ResultCheck(Result result)
at Microsoft.SqlServer.Import.Wizard.InsertData.DoWork()
at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()
===================================
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476


Server Name: WRKST/SQLEXPRESS
Error Number: -2
Severity: 11
State: 0


Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
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.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.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalTransaction.Rollback()
at System.Data.SqlClient.SqlTransaction.Rollback()
at Microsoft.SqlServer.Prose.Import.BcpProcess.<>c_DisplayClass60_0.<CreateTableAndInsertDataIntoDb>b_0()
at Microsoft.SqlServer.Prose.Import.BcpProcess.ExecuteOperation(Action operation)
===================================
The wait operation timed out


So, I followed several guidelines available but I haven't fixed this problem and it is very frustrating.

  • I increased the size of my database (overgrow setting) to 2TB since I know I would need approximately 980GB for the data that I do have so I have given some overhead.

  • I also, increased the timeout settings under Tools>Options>Designers>Transaction-timeout after option to 5 hours.

  • I have increased the timeout settings under my server properties: Advanced > Parallelism > Query wait to -1

I have tried to alter the registry settings under HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server but I cannot find any registry value relating to the timeout setting others have specified.

Any help is greatly appreciated. if you require any more information please ask.









sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Did you consider splitting the large file into several smaller pieces? If the file is a text, you can even write a tool in some programming language.

0 Votes 0 ·

1 Answer

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

Since you are using the import wizard - which creates an SSIS package and executes that package - it might be easier to debug if you saved the package, opened the package in SSDT and manually executed it from there. At least you would be able to see what portion of the process it is hanging on...

I suspect it is hanging because you are trying to open a 72GB file - but that is just a guess.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.