Itgenspr004: Could not load all metadata from SQL Server

Vragen
, ,
1

Normaal gesproken laden we dagelijks data van ExactOnline via Invantive Data Hub. Dit gaat via simpele code:

create or replace table TableName
as
select * FROM Exactonlinexml.XML.TableName

Sinds afgelopen maandag geeft deze echter de volgende error:

2025-03-24 22:20:47.079 Exclamation itgencun016: Uitroep itgenspr004: Could not load all metadata from SQL Server.

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

The wait operation timed out.
Please read details provide the errors occurring during loading of metadata.
2025-03-24 22:20:49.081 Error itgenspr004: System.ComponentModel.Win32Exception
System.Data.SqlClient.SqlException
InvantiveSystemException
   at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
   at System.Environment.get_StackTrace()
   at Invantive.Basics.ErrorConstants.GetEnvironmentStackTrace(Boolean ignoreErrors, String valueOnError)
   at Invantive.Basics.ExtensionMethods.GetAllStackTraces(Exception exception, GlobalState owner, ExecutionOptions executionOptions, Boolean deepestFirst, Boolean useDefaultStackTrace)
   at Invantive.Producer.ConsoleUserInteraction.PresentErrorToUser(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, ErrorDialogInformation errorDialogInformation)
   at Invantive.Data.Error.HandleUnexpectedException(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, DataContextBase dataContext, IProviderManager manager, Nullable`1 messageId, Exception exceptionRaised, Boolean askContinue, String customErrorMessageWithoutCode, Boolean showErrorToUser, String parameters, LoggingLevel loggingLevel, String applicationNameWithVersion, String applicationName, String applicationVersion, String windowTitle, Dictionary`2 metrics, IBasicProviderInfo originatingProvider, String kindRequest)
   at Invantive.Data.Error.HandleUnexpectedException(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, DataContextBase dataContext, Nullable`1 messageId, Exception exceptionRaised, Boolean askContinue, String customErrorMessageWithoutCode, Boolean showErrorToUser, String parameters, LoggingLevel loggingLevel, String applicationNameWithVersion, String applicationName, String applicationVersion, String windowTitle, Dictionary`2 metrics, IBasicProviderInfo originatingProvider, String kindRequest)
   at Invantive.Data.Error.HandleUnexpectedException(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, Nullable`1 messageId, Exception exceptionRaised, Boolean askContinue, String customErrorMessageWithoutCode, Boolean showErrorToUser, String parameters, LoggingLevel loggingLevel, String applicationNameWithVersion, String applicationName, String applicationVersion, String windowTitle, Dictionary`2 metrics, IBasicProviderInfo originatingProvider)
   at Invantive.Data.Error.HandleUnexpectedException(GlobalState owner, ExecutionOptions executionOptions, Nullable`1 messageId, Exception exceptionRaised, Boolean askContinue, String customErrorMessageWithoutCode, Boolean showErrorToUser, String parameters, LoggingLevel loggingLevel, String applicationNameWithVersion, String applicationName, String applicationVersion, String windowTitle, Dictionary`2 metrics, IBasicProviderInfo originatingProvider)
   at Invantive.Producer.ApplicationErrorHelper.HandleUnexpectedException(GlobalState owner, ExecutionOptions executionOptions, Nullable`1 messageId, Exception exceptionRaised, Boolean askContinue, String customErrorMessage, Boolean showErrorToUser, String parameters, LoggingLevel level, Dictionary`2 metrics, IBasicProviderInfo originatingProvider)
   at IDH.Y.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`3 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
   at IDH.Y.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`3 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification)
   at IDH.Y.D(GlobalState , ExecutionOptions , String[] )
   at Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
2025-03-24 22:20:49.081 Information itgendhb220: Einde programma-uitvoering met exitcode 2.
...

Hoe kan dit probleem opgelost worden?

2

Analyse

Waarschijnlijk ziet het SQL-statement dat een itgenspr004 geeft er iets anders uit:

create or replace table TableName@DOEL
as
select * FROM Exactonlinexml.XML.TableName@BRON

met twee aliases toegevoegd. De itgenspr004 verwijst zoals blijkt uit melding naar SQL Server. De SQL Server-verbinding heeft langer nodig dan de ingestelde time-out om de metadata op te halen (de lijst van tabelnamen en dergelijke). De metadata wordt bij het verbinden (lijst tabellen) of tijdens (kolomnamen) het eerste SQL-statement op SQL Server opgehaald.

Een onderliggende foutcode is itgenclr038 die leidt tot de extra tekst The wait operation timed out. Merk op dat de itgenspr004 in nieuwe releases vervangen kan zijn door itgenclr104 voor eenvoudiger duiding.

Vergelijkbaar geval

Een vergelijkbaar geval is:

De achterliggende SQL Server foutmelding is ook terug te vinden via bijvoorbeeld een search engine:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out.

Inside Invantive Data Hub

De volledige callstack is:

   at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
   at System.Environment.get_StackTrace()
   at Invantive.Basics.ErrorConstants.GetEnvironmentStackTrace(Boolean ignoreErrors, String valueOnError)
   at Invantive.Basics.ErrorToTrace.CurrentDomain_FirstChanceException(Object sender, FirstChanceExceptionEventArgs e)
   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.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   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 Invantive.Data.AnsiSqlProviderBase.RetrieveSimpleResultSet(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IConnection pooledConnection, String actionSql, ParameterList parameters, String loggingTableName)
   at Invantive.Data.Providers.SqlServer.SqlServerProvider.RegisterDataContainerMetadata(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IConnection connection, DataContainerMetadata dataContainerMetadata)
   at Invantive.Data.AnsiSqlProviderBase.GetDataContainerMetadata(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IConnection pooledConnection)
   at Invantive.Data.AnsiSqlProviderBase.GetDataContainerMetadata(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep)
   at Invantive.Data.AnsiSqlProviderBase.UUC(GlobalState , ExecutionOptions , SqlExecutionStep , ObjectSpecification , DataContainerColumnMetadata& )
   at Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)
   at Invantive.Sql.V1.DdlStatementTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, ParameterList parameters, QueryObject additionalFilters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, ParameterList parameters, QueryObject additionalFilters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryPlan.ForcePlan(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep)
   at Invantive.Sql.V1.QueryPlan.GetFields(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep)
   at Invantive.Sql.V1.QueryPlan.GetFieldCount(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep)
   at Invantive.Data.QueryPlanCacheEntry.VDP.A(IQueryPlan )
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.QueryPlanCacheEntry..ctor(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, String sqlStatement, IQueryPlan[] queryPlans, ParameterList parameters)
   at Invantive.Sql.V1.SqlEngine.ZP(GlobalState , ExecutionOptions , SqlExecutionStep , QueryPlan[] , String , ParameterList , ResultSetAuditInformation )
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, SqlExecutionStep sqlExecutionStep, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.KT(GlobalState , ExecutionOptions , SqlExecutionStep , String , ParameterList , String )
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at IDH.Y.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`3 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
   at IDH.Y.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`3 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification)
   at IDH.Y.D(GlobalState , ExecutionOptions , String[] )
   at Invantive.Producer.QueryEngine.Program.Main(String[] arguments)

Het voorkomen van RegisterDataContainerMetadata geeft voor Invantive ook aan dat de foutmelding optreedt binnen het ophalen van metadata bij SQL Server. Dit is ook mooi zichtbaar aan System.Data.SqlClient.SqlDataReader.get_MetaData(): de code van Microsoft om de metadata op te halen.

De bijbehorende (interne) query op SQL Server is:

select /* itgenspr062 */ tbe.table_catalog
,      tbe.table_schema
,      tbe.table_name
,      tbe.table_type
,      pk_cln.name primary_key
from   tempdb.INFORMATION_SCHEMA.TABLES tbe
join   tempdb.sys.schemas sma 
on     sma.name               = tbe.table_schema collate CATALOG_DEFAULT
left
outer
join   tempdb.sys.tables st
on     st.name                = tbe.table_name collate CATALOG_DEFAULT
and    st.schema_id           = sma.schema_id
left
outer
join   tempdb.sys.indexes ik
on     ik.object_id           = st.object_id
and    ik.is_primary_key      = 1
left
outer
join   tempdb.sys.index_columns cik
on     cik.index_id           = ik.index_id
and    cik.object_id          = st.object_id
and    cik.is_included_column = 0
and    cik.index_column_id    = 1
left
outer
join   tempdb.sys.all_columns pk_cln
on     pk_cln.column_id       = cik.column_id
and    pk_cln.object_id       = st.object_id

Merk op dat deze op tempdb uitgevoerd wordt. Normaliter hoort deze meestal leeg te zijn. Ook dit geeft een signaal dat er iets mis is met de SQL Server-omgeving.

Advies

Advies is om contact op te nemen met uw SQL Server-beheerder en de juiste en vlotte werking te controleren van bovenstaande query.

Eventueel kunt u in overleg met uw SQL Server-beheerder de timeout verhogen via bijvoorbeeld command-timeout-sec voor het uitvoeren van een statement. Het is echter de verwachting dat dit enkel een tijdelijk doekje voor het bloeden zal zijn met negatieve zij-effecten zoals mogelijkerwijs vele malen tragere werking.

3

Het probleem is nu opgelost. Nadat we de server een reboot gaven werkte het weer. Onze tempdb zat helemaal vast en daar was het probleem waarschijnlijk door veroorzaakt.

4

Dit topic is 3 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.