Question

I ve recently encountered the error:

System.Data.SqlClient.SqlException: The transaction log for database mydatabase is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

on one of my windows services. It s supposed to retry after catching an Sql Exception, what I didn t expect was that it seemed like the data was still going through (I m using an SqlBulkCopy btw) regardless of it throwing an exception. I ve never encountered this scenario before.

I d like to know if there are other scenarios where such a thing like this might happen, and if this thing is entirely possible at all in the first place?

EDIT:

System.Data.UpdateException: An error occurred while updating the entries. See the InnerException for details. ---> System.Data.SqlClient.SqlException: The transaction log for database MY_DB is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.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.ExecuteReader(CommandBehavior behavior) at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary2 identifierValues, List1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) --- End of inner exception stack trace --- at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave) at System.Data.Objects.ObjectContext.SaveChanges()

PS. If anyone knows the error code to the above exception, that would help a great deal as well.

Answer 1

if this thing is entirely possible at all in the first place?

Yes it is possible. It depends what your transaction management code does in the exception case. For example:

Connection connection = ConnectionFactory.create();

try {
    DoWorkOn(connection);
}
catch (Exception e) {
    Log.Message("SQL Problem", e);
}
finally {
    connection.commit();
}

... would always try to commit. This behaviour is built in to some well known transaction management libraries (J2EE specifies this behaviour for application exceptions).

I m less familiar with .NET - if you post a full stack trace somebody might be able to give you a better answer based on the transaction management software in use.

Answer 2

An SQL command could result in a timeout error, but still happen anyway. You should plan UPDATEs and DELETEs such that they could be called many times but only perform the change once. For example

UPDATE MyTable SET DataField=1,LastChanged=GETUTCDATE()  
   WHERE PrimaryKeyField=x AND DataField=0

This way, if DataField no longer has the value you expected, the UPDATE will do nothing and leave the LastChanged field unchanged.

友情链接