Hi, we have a windows service that use a SQL Server 2005 database located on the same machine, with .Net 2.0.

The service works fine for some days, and randomly it receives a timeout exception from the database. After some other few timeout exceptions, the database seems to be freezed, because there are no other exceptions.

I don't think that increasing the command timeout could solve the problem, because the queries are very simple (SELECT * FROM x where y = z), returning few data.

Restarting the service solves the problem.

What could be the cause of the exception?

Daniele.

The details of the exceptions are :

Message: HandlingInstanceID: f4eb26cc-90bb-4375-b443-337e277b1fec

An exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred and was caught.

09/24/2007 07:39:16 Type : SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.0.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27 Message : An exception was caught during the execution of a retrieval query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. Source : SD.LLBLGen.Pro.ORMSupportClasses.NET20 Help link : QueryExecuted : Query: SELECT [dbo].[Session].[SessionSerial], [dbo].[Session].[SessionId], [dbo].[Session].[UserId], [dbo].[Session].[StartedOnUTC] AS [StartedOnUtc], [dbo].[Session].[ClosedOnUTC] AS [ClosedOnUtc] FROM [dbo].[Session] WHERE ( ( [dbo].[Session].[SessionId] = @SessionId1)) Parameter: @SessionId1 : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "urn:uuid:65758377-2edf-4379-86ed-0d3c6c499517".

Parameters : System.Data.SqlClient.SqlParameterCollection RuntimeVersion : 2.0.0.0 RuntimeBuild : 02092007 Data : System.Collections.ListDictionaryInternal TargetSite : System.Data.IDataReader Execute(System.Data.CommandBehavior) Stack Trace : at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket) at BSS.Vortex.Administration.Helper.GetSessionBySessionId(String ptSessionId) in ...

Inner Exception : --------------- Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Source : .Net SqlClient Data Provider Help link : Errors : System.Data.SqlClient.SqlErrorCollection Class : 11 LineNumber : 0 Number : -2 Procedure : Server : SVR09 State : 0 ErrorCode : -2146232060 Data : System.Collections.ListDictionaryInternal TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean) Stack Trace : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

Hi Danielle, can you paste the code that produces this query:

Query: SELECT [dbo].[Session].[SessionSerial], [dbo].[Session].[SessionId], [dbo].[Session].[UserId], [dbo].[Session].[StartedOnUTC] AS [StartedOnUtc], [dbo].[Session].[ClosedOnUTC] AS [ClosedOnUtc] FROM [dbo].[Session] WHERE ( ( [dbo].[Session].[SessionId] = @SessionId1))
    Parameter: @SessionId1 : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "urn:uuid:65758377-2edf-4379-86ed-0d3c6c499517".

make sure all your transactions are committed/rolledback. also make sure you dispose of objects when you're done with them. make sure the column used in the where clause is indexed as well.

It could also be there is a long running tasks accessing the db from another application/module which is blocking the services requests, causing a timeout on your end.

Hi, this is the code (in vb.net)

Dim loDbSessions As LLBL.Setting.HelperClasses.EntityCollection(Of LLBL.Setting.EntityClasses.SessionEntity)

Dim oBucket As RelationPredicateBucket

Using da As New LLBL.Setting.DatabaseSpecific.DataAccessAdapter _ 
        ConnectionHelper.GetSettingConnectionString, False, CatalogNameUsage.Clear, _
        String.Empty)

    oBucket = New RelationPredicateBucket

    oBucket.PredicateExpression.Add(LLBL.Setting.HelperClasses.SessionFields.SessionId = ptSessionId)

    loDbSessions = New LLBL.Setting.HelperClasses.EntityCollection(Of LLBL.Setting.EntityClasses.SessionEntity) _
        (New LLBL.Setting.FactoryClasses.SessionEntityFactory)

    da.FetchEntityCollection(loDbSessions, oBucket)
End Using 

The table has only about 250.000 rows.

Thank you all, Daniele.

Query: SELECT [dbo].[Session].[SessionSerial], [dbo].[Session].[SessionId], [dbo].[Session].[UserId], [dbo].[Session].[StartedOnUTC] AS [StartedOnUtc], [dbo].[Session].[ClosedOnUTC] AS [ClosedOnUtc] FROM [dbo].[Session] WHERE ( ( [dbo].[Session].[SessionId] = @SessionId1)) Parameter: @SessionId1 : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "urn:uuid:65758377-2edf-4379-86ed-0d3c6c499517".

Running the above statement against the database won't cause a timeout exception, will it?

The service works fine for some days, and randomly it receives a timeout exception from the database

There has to be a valid cause rather than randomness. Would you please use the SQL Profiler? run ti and leave it open and go back and check its tracing log when you receive such error again. Maybe you ran out of connections, maybe some connections are kept open and haven't returned to the connection pooling, so it might happen that the application can't get a new collection. Also it might be a dead lock or something, so the Profiler can be the only way to debug this.

Sounds indeed like a deadlock or at least a wait for a lock which then runs into a timeout. Please check if you indeed commit all the transactions you start.

Also, if SqlServer seems to 'freeze' you can inside SSMS check which objects are locked by which process. This could reveal which tables are locked and therefore which transactions aren't committed properly.

Thank You. I will attach the profiler. Daniele.