Error with MS SQL Server when importing large flat files.

Constantinos Pouyioukka 6 Reputation points
2021-03-31T09:23:04.217+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jeffrey Williams 1,886 Reputation points
    2021-04-01T15:16:13.353+00:00

    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.

    0 comments