Connection Timeout when processing cube

I am getting a connection timeout error when processing a cube with measure group containing 4mil rows or so. It errors after 5 mins and after about 3mil rows have been read so it's probably not the ExternalCommandTimeout issue. The Fact table for the measure group is created using a named query. I would appreciate any insight into why the error is thrown after 5 mins. Here's part of the error:

===================================

The connection was lost. Close all processing dialog boxes and try processing the object again. (Microsoft Visual Studio)

===================================

The connection either timed out or was lost. (Microsoft.AnalysisServices)

Program Location:

at Microsoft.AnalysisServices.XmlaClient.EndRequest()
at Microsoft.AnalysisServices.XmlaClient.SendExecuteAndReadResponse(Boolean skipResults, Boolean throwIfError)
at Microsoft.AnalysisServices.AnalysisServicesClient.Execute(String command)
at Microsoft.AnalysisServices.Server.Execute(String command)
at Microsoft.AnalysisServices.ManagementDialogs.OlapProcessProgressContainer.ProcessObjects()

===================================

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.TcpStream.GetDataType()

===================================

An existing connection was forcibly closed by the remote host (System)

Dont think this has anything to do with ExternalCommandTimeout.

Try processing your cube using SQL Management Studio.
For that first deploy your project and in the project's properties in the deployment tab select Processing option = "Do Not Process".
Then open a SQL Management studio, connect to Analysis Services, navigate to your cube and try to process it. See if you get any errors.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I already tried processing inside SQL Management Studio but got the same error. I also ran the named query inside SQL Management Studio and it took about 10 mins to return all 4mil rows so I know the named query should be good too...... |||

Couplde of things:

Take a look at the msmdsrv.log located in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log folder.
Do you see there any messages around time you trying to process your cube.

If you havent yet, install SP1 of SQL Server and see if the same behavior persists.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Fixed. It was due to networking issues in the office. Thanks guys!

Connection Timeout when processing cube

I am getting a connection timeout error when processing a cube with measure group containing 4mil rows or so. It errors after 5 mins and after about 3mil rows have been read so it's probably not the ExternalCommandTimeout issue. The Fact table for the measure group is created using a named query. I would appreciate any insight into why the error is thrown after 5 mins. Here's part of the error:

===================================

The connection was lost. Close all processing dialog boxes and try processing the object again. (Microsoft Visual Studio)

===================================

The connection either timed out or was lost. (Microsoft.AnalysisServices)

Program Location:

at Microsoft.AnalysisServices.XmlaClient.EndRequest()
at Microsoft.AnalysisServices.XmlaClient.SendExecuteAndReadResponse(Boolean skipResults, Boolean throwIfError)
at Microsoft.AnalysisServices.AnalysisServicesClient.Execute(String command)
at Microsoft.AnalysisServices.Server.Execute(String command)
at Microsoft.AnalysisServices.ManagementDialogs.OlapProcessProgressContainer.ProcessObjects()

===================================

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)

Program Location:

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.TcpStream.GetDataType()

===================================

An existing connection was forcibly closed by the remote host (System)

Dont think this has anything to do with ExternalCommandTimeout.

Try processing your cube using SQL Management Studio.
For that first deploy your project and in the project's properties in the deployment tab select Processing option = "Do Not Process".
Then open a SQL Management studio, connect to Analysis Services, navigate to your cube and try to process it. See if you get any errors.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I already tried processing inside SQL Management Studio but got the same error. I also ran the named query inside SQL Management Studio and it took about 10 mins to return all 4mil rows so I know the named query should be good too...... |||

Couplde of things:

Take a look at the msmdsrv.log located in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log folder.
Do you see there any messages around time you trying to process your cube.

If you havent yet, install SP1 of SQL Server and see if the same behavior persists.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Fixed. It was due to networking issues in the office. Thanks guys!

Connection Timeout

Hi, I have a client application that submits heavy processing query to an instance of Sql Server 2005 (Enteriprise Edition). After a few seconds the following exception is raised: "The time available has expired before the completion of the operation or the server is not responding". If I submit to the same application light processing queries the exception is not raised.
The client application is written in VB.net and uses ADO.net. (SqlConnection, SqlCommand,.. classes). The "Timeout connection" property is set to 1000.
Any suggestion?
Thank you.

Hi Ivan,

The ConnectionTimeout property of SqlConnection defines the time boundaries to open a physical connection to the SQL Server. In your case, the physical connection seems to be established within a reasonable frame. However, after the connection is up, the client and the server begin exchanging security information as part of the login process. This is related to a different "login timeout", which unfortunately you don't seem to be able to control from SqlClient.

What is happening on the server during the login process is related to the SOS scheduler operations - I won't go into deep detail, but in a nutshell all the tasks (including processing of login information) are queued. If the queue length is very long, the login information processing may be delayed beyond the login timeout and you will experience your symptoms above. There could also be other factors - the login processing makes calls to the security subsystem, if the entire SQL machine is under heavy stress, those may take longer time, too. You should approach this as a perf problem and use appropriate tools like perfmon, the SOS DMVs, etc.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Connection Timeout is the timeout for connecting to SQL Server. What you need is COMMAND timeout. Set COMMAND timeout=0 for indefinite period.

Command timeout has to be set on the command object - because it is related to a particular SQL processing.