VCAC Database Exception Error executing query usp_SelectAgentCapabilities

[p_nataraj]

Well the database is rechable, DNS is fine, time is synced but still i see the below exception in the VCAC server logs

The Agent datacollection status is "in queue" for very very long time.

INNER EXCEPTION: System.ApplicationException: Error executing query usp_SelectAgentCapabilities  ---> System.Data.SqlClient.SqlException: MSDTC on server 'CLOUD-DB' is unavailable.

   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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)

   at System.Data.SqlClient.TdsParser.GetDTCAddress(Int32 timeout, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlInternalConnectionTds.GetDTCAddress()

   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)

   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReaderNoRetry(RecordReader`1 recordReader, String storedProcedure, String whereClause, SqlParameter[] collection, Boolean firstRecordOnly)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   --- End of inner exception stack trace ---

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   at DynamicOps.VMPS.DataAccess.AgentManager.GetAgentCapabilities(Guid agentID)

   at DynamicOps.VMPS.DataAccess.AgentManager.ReadInstance(IDataRecord record, Agent& agent)

   at DynamicOps.VMPS.DataAccess.RecordReader`1.ReadRecord(IDataRecord record, List`1 instances)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReaderNoRetry(RecordReader`1 recordReader, String storedProcedure, String whereClause, SqlParameter[] collection, Boolean firstRecordOnly)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

[UTC:2014-10-05 18:50:58 Local:2014-10-06 00:20:58] [Error]: <?xml version="1.0" encoding="utf-16"?>

<boolean>false</boolean>

CollectedDataImportService: Ignoring exception:

System.ApplicationException: Error executing query usp_SelectManagementEndpoint  ---> System.ApplicationException: Error executing query usp_SelectEntityProperties  ---> System.Data.SqlClient.SqlException: MSDTC on server 'CLOUD-DB' is unavailable.

   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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)

   at System.Data.SqlClient.TdsParser.GetDTCAddress(Int32 timeout, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlInternalConnectionTds.GetDTCAddress()

   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)

   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReaderNoRetry(RecordReader`1 recordReader, String storedProcedure, String whereClause, SqlParameter[] collection, Boolean firstRecordOnly)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   --- End of inner exception stack trace ---

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   at DynamicOps.VMPS.DataAccess.RecordManager`1.LoadProperties(T obj)

   at DynamicOps.VMPS.DataAccess.ManagementEndpointManager`1.ReadInstance(IDataRecord record, T& managementEndpoint)

   at DynamicOps.VMPS.DataAccess.RecordReader`1.ReadRecord(IDataRecord record, List`1 instances)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReaderNoRetry(RecordReader`1 recordReader, String storedProcedure, String whereClause, SqlParameter[] collection, Boolean firstRecordOnly)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   --- End of inner exception stack trace ---

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, Boolean firstRecordOnly, SqlParameter[] collection)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteSingleRead(RecordReader`1 recordReader, String storedProcedure, SqlParameter[] collection)

   at DynamicOps.VMPS.DataAccess.RecordManager`1.Select(Guid id)

   at DynamicOps.VMPS.DataAccess.DataAccessLayer.<>c__DisplayClass64.<LoadManagementEndpointWithRetry>b__63()

   at DynamicOps.Common.Exceptions.RetryTransactionException.RetryIfNecessary(Action action)

   at DynamicOps.VMPS.Service.CollectedDataImportService.<>c__DisplayClass5.<CreateWorkItemsForOverDueCollectionJobs>b__3()

   at DynamicOps.VMPS.DataAccess.DataAccessLayer.LoadWorkflowDefinition(RepositoryModelEntities repoContext, String workflowName, String workflowSuffix, String machineType, ManagementEndpointGetter endpointGetter)

   at DynamicOps.VMPS.Service.CollectedDataImportService.CreateWorkItemsForOverDueCollectionJobs()

   at DynamicOps.VMPS.Service.CollectedDataImportService.DataCollectionCallback(Object state)

INNER EXCEPTION: System.ApplicationException: Error executing query usp_SelectEntityProperties  ---> System.Data.SqlClient.SqlException: MSDTC on server 'CLOUD-DB' is unavailable.

   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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)

   at System.Data.SqlClient.TdsParser.GetDTCAddress(Int32 timeout, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlInternalConnectionTds.GetDTCAddress()

   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)

   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReaderNoRetry(RecordReader`1 recordReader, String storedProcedure, String whereClause, SqlParameter[] collection, Boolean firstRecordOnly)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   --- End of inner exception stack trace ---

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

   at DynamicOps.VMPS.DataAccess.RecordManager`1.LoadProperties(T obj)

   at DynamicOps.VMPS.DataAccess.ManagementEndpointManager`1.ReadInstance(IDataRecord record, T& managementEndpoint)

   at DynamicOps.VMPS.DataAccess.RecordReader`1.ReadRecord(IDataRecord record, List`1 instances)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReaderNoRetry(RecordReader`1 recordReader, String storedProcedure, String whereClause, SqlParameter[] collection, Boolean firstRecordOnly)

   at DynamicOps.VMPS.DataAccess.ConnectionFactory`1.ExecuteReader(RecordReader`1 recordReader, String storedProcedure, String whereClause, Boolean firstRecordOnly, SqlParameter[] collection)

[abhilashhb]

Please do not paste the log in the question. Always attach the log file with your discussion.

You will have to see the DTC communication between the DB and IaaS server. Download DTCPing tool and check it.

To start with, see if there's in any firewall between them and also if port 135 is open for communication in both the directions.

[p_nataraj]

Thanks for the response, DTCPing works and there is firewall is indeed disabled on both the machines.

Invoking RPC method on cloud-db

RPC test is successful

++++++++++++RPC test completed+++++++++++++++

Please start PING from cloud-db to complete the test

Please send following LOG to Microsoft for analysis:

  Partner LOG: CLOUD-DB8100.log

  My LOG: CLOUD-VCACIAAS4272.log

++++++++++++Start Reverse Bind Test+++++++++++++

Received Bind call from CLOUD-DB

Trying Reverse Bind to CLOUD-DB

Reverse Binding success: CLOUD-VCACIAAS-->CLOUD-DB

++++++++++++Reverse Bind Test ENDED++++++++++

++++++++++++Start DTC Binding Test +++++++++++++

Trying Bind to CLOUD-DB

Received reverse bind call from CLOUD-DB

Binding success: CLOUD-VCACIAAS-->CLOUD-DB

++++++++++++DTC Binding Test END+++++++++++++

Session idle timeout over, tearing down session

Session is shuting down

[abhilashhb]

Now login into your vCAC and navigate to compute resource. open data collection for compute resource(all if you have more than one) and post a screenshot.

[p_nataraj]

The issue is resolved with this StorageGumbo: Using a Cloned VM as a SQL Server - Gotcha for vCAC Install