How to track down this error

Hi,
We recently got an exception in the SOA-solution I'm working on and none of
the developers have seen this error before, and we are not sure where to
begin. We are running against a busy sql server 2005 and as I understand it
logging cannot be turned on. The exception message is as follows.
System.Data.SqlClient.SqlException: The current transaction cannot be
committed and cannot support operations that write to the log file. Roll
back the transaction.
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.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.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
The exception occures running ExecuteNonQuery against one of the stored
procedures in the database (which I'm not allowed to reproduce here). We
cannot identify anything wrong with the procedure.
Do anyone have any idea what a likely cause (or unlikely, yet possible) for
this error? Or ideas on how to track it down.
Thanks,
Morten WennevikOn Sep 26, 11:36 am, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> 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.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.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible) for
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Morten,
The error is easy to reproduce:
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
-- at this time transaction is not committable
COMMIT
END CATCH
/*
Msg 3930, Level 16, State 1, Line 7
The current transaction cannot be committed and cannot support
operations that write to the log file. Roll back the transaction.
*/
if that is your case, you should change your code to:
BEGIN TRAN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
-- at this time transaction is not committable
IF XACT_STATE() = -1
ROLLBACK
ELSE
COMMIT
END CATCH|||On Sep 26, 6:36 pm, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none =of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand= it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb=je=ADct
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> 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.InternalExecuteNonQuery(DbAsyncRes=ult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible) f=or
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Check if the transaction did not violate any constraint and that you
did not try to insert wrong data type to one of the columns.|||Hi Alex,
Thanks for your suggestion. What we believe turned out to be the error was
a convert to int where the input parameter was '2007-09-27' instead of
'20070927'. When we got logging turned on, this generated an exception,
which was caught, and the catch block did indeed do RaiseError with an error
severity above 11, but somehow this didn't pass control back to .Net. When
the query much later tried to commit all the jobs it could not, since an
exception had occured. The actual Commit and Rollback is handled at a
higher level than inside the catch block.
Morten
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1190825499.191973.201250@.g4g2000hsf.googlegroups.com...
> On Sep 26, 11:36 am, "Morten Wennevik" <MortenWenne...@.hotmail.com>
> wrote:
>> Hi,
>> We recently got an exception in the SOA-solution I'm working on and none
>> of
>> the developers have seen this error before, and we are not sure where to
>> begin. We are running against a busy sql server 2005 and as I understand
>> it
>> logging cannot be turned on. The exception message is as follows.
>> System.Data.SqlClient.SqlException: The current transaction cannot be
>> committed and cannot support operations that write to the log file. Roll
>> back the transaction.
>> 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.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.InternalExecuteNonQuery(DbAsyncResult
>> result, String methodName, Boolean sendToPipe)
>> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
>> The exception occures running ExecuteNonQuery against one of the stored
>> procedures in the database (which I'm not allowed to reproduce here). We
>> cannot identify anything wrong with the procedure.
>> Do anyone have any idea what a likely cause (or unlikely, yet possible)
>> for
>> this error? Or ideas on how to track it down.
>> Thanks,
>> Morten Wennevik
> Morten,
> The error is easy to reproduce:
> SET XACT_ABORT ON
> BEGIN TRAN
> BEGIN TRY
> SELECT 1/0
> END TRY
> BEGIN CATCH
> -- at this time transaction is not committable
> COMMIT
> END CATCH
> /*
> Msg 3930, Level 16, State 1, Line 7
> The current transaction cannot be committed and cannot support
> operations that write to the log file. Roll back the transaction.
> */
> if that is your case, you should change your code to:
> BEGIN TRAN
> BEGIN TRY
> SELECT 1/0
> END TRY
> BEGIN CATCH
> -- at this time transaction is not committable
> IF XACT_STATE() = -1
> ROLLBACK
> ELSE
> COMMIT
> END CATCH
>|||Hi Adi,
Thanks for your suggestion. What we believe turned out to be the error was
a convert to int where the input parameter was '2007-09-27' instead of
'20070927'. When we got logging turned on, this generated an exception,
which was caught, and the catch block did indeed do RaiseError with an error
severity above 11, but somehow this didn't pass control back to .Net. When
the query much later tried to commit all the jobs it could not, since an
exception had occured. The actual Commit and Rollback is handled at a
higher level than inside the catch block.
Morten
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190825538.689369.104420@.d55g2000hsg.googlegroups.com...
On Sep 26, 6:36 pm, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none
> of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand
> it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje­ct
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> 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.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible)
> for
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Check if the transaction did not violate any constraint and that you
did not try to insert wrong data type to one of the columns.