Hi,

I'm troubleshooting an ASP.NET 1.1 application that uses NHibernate. It intermittently reports the following error:

SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I've never been able to reproduce it, but this seems to happen mostly during periods of very light load - in the evening, in the middle of the night, on weekends - which is not when I'd expect the DB server to time out. Apart from that, I cannot see any pattern - the error occurs during inserts and updates of all sorts of different objects, so it's not any particular table that takes a long time to update.

I'd really appreciate any suggestions, because I have no idea what to do about this problem!

Hibernate version:

NHibernate 1.0.0.0

Code between sessionFactory.openSession() and session.close():

ISession session = container.Get();
if (session == null || !session.IsOpen)
{
session = sessionFactory.OpenSession();
container.Put(session);
}

ITransaction transaction = session.Transaction;
bool proxyStartedTransaction = false;
if (session.Transaction == null)
{
transaction = session.BeginTransaction();
proxyStartedTransaction = true;
}

try
{
object retVal;

try
{
retVal = method.Invoke(target, parameters);
}
catch (TargetInvocationException e)
{
// Log and rethrow
}

if (transaction != null && proxyStartedTransaction)
{
transaction.Commit();
}
else
{
session.Flush();
}

return retVal;
}
catch (Exception ex)
{
if (transaction != null && proxyStartedTransaction)
{
logger.Error("Rolling back transaction after Hibernate exception.", ex);

transaction.Rollback();
session.Close();
}

throw;
}

Normally (if no exception occurs) the session is closed in Application_EndRequest - is this correct?

Full stack trace of any exception that occurs:

2006-04-09 19:04:36,108 [4092] ERROR ADOException - could not insert: [au.com.venturelogic.linkme.common.managers.userProfile.NetworkerProfile#a31e47fca6af46c4b5c6788c918f4870]
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, SqlString[] sql, Object obj, ISessionImplementor session)
2006-04-09 19:04:36,108 [4092] ERROR SessionImpl - could not synchronize database state with session
NHibernate.ADOException: could not insert: [au.com.venturelogic.linkme.common.managers.userProfile.NetworkerProfile#a31e47fca6af46c4b5c6788c918f4870] ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, SqlString[] sql, Object obj, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, SqlString[] sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledInsertion.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()
2006-04-09 19:04:36,108 [4092] ERROR HibernateTransactionProxy - [fd495e3efe36409caa427d94eb479ef3] [] [ANON-ROLE] [APP2] [203.164.15.162] Rolling back transaction after Hibernate exception.
NHibernate.ADOException: could not insert: [au.com.venturelogic.linkme.common.managers.userProfile.NetworkerProfile#a31e47fca6af46c4b5c6788c918f4870] ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, SqlString[] sql, Object obj, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, SqlString[] sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.NormalizedEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledInsertion.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()
at NHibernate.Impl.SessionImpl.Flush()
at NHibernate.Transaction.AdoTransaction.Commit()
at HibernateTransactionProxy.InvocationHandler(Object target, MethodBase method, Object[] parameters)

Name and version of the database you are using:

SQL 2000 SP3

This is very likely a database issue unrelated to NHibernate. I have seen similar behaviour before with SQL Server 2000 due to its taking excessive time to grow the database when records are added. Try manually growing the database by 20-30% and see if that has any effect.

Well, it wasn't the database growth, but you're right - it's unrelated to NHibernate. It turned out that there was Snapshot Replication configured, which was regularly locking all the tables in the database!