sql - Deadlock vs. Logging -

web application. c#, sql 2k8

in list, there lot of things going on, before actual dynamic sql gets executed, , returns desired records.

recently added code whole app (deadlock issues) transaction commit, , rollback , retry x times, whole 9 yards.

that big plus, needed that.

but

in order debug possible issues list, saving dynamic sql string in log table (which doesn't store old records last couple of weeks)

problem is: right there no log, if list crashes. because of rollback stuff ...

my best idea far call list twice:

1. "check" - mode, create dynamic sql, save in log tbl, not exec

2. "list" - mode either:

2a. recalculate dynamic sql

or

2b. reuse dynamic sql created "check - mode

either 2a or 2b, should not have huge performance issue, because expensive part of list stored procedure actual execution of dynamic sql. "check" - mode basic string concatenation.

the deadlock retry part second db call, good!

still. not happiest person idea. wonder if there better way achieve this.

you can use (table) variables along try catch blocks save data during transaction. variables kept after rollback.

if translations inside stored procedure, can this:

begin try     begin tran      declare @sql nvarchar(max)     set @sql = 'select 1'      raiserror('a',16,1)      exec (@sql)      commit  end try begin catch     rollback     select @sql --write log table  end catch 

and if transactions outside of sp - in c# code (which in general better idea), can send variable app raiserror along actuall error happened. this:

begin try      declare @sql nvarchar(max)     set @sql = 'select 1'      raiserror('a',16,1)      exec (@sql)  end try begin catch      declare @errormessage nvarchar(4000)     declare @errorseverity int     declare @errorstate int      select          @errormessage = 'error: ' + error_message() + '; sql: ' + @sql,         @errorseverity = error_severity(),         @errorstate = error_state()      raiserror (@errormessage,@errorseverity,@errorstate)  end catch