If i put the two nodes ( Primary and Secondary ) in Synchronous mode with Auto failover and 3rd node with Asynchronous with Manual failover, now it seems to work....

Hmmmm...That is really interesting. I have nothing else running on this server but this one sproc opened in SSMS which only creates one spid. Wondering why it shows 22.

I had to do something similar. I created a job with 2 steps, first step was to rename, load data from existing trace and second step was to...

Got it.

Looks like my Try/Catch is not trapping all errors. For example if the table name is wrong it doesn't log.

But it should be able to rollback right? If the server crashes i am thinking once the server is back it should rollback all the uncommitted transactions. Really i am...

Are all errors logged on try catch ? I ran the code and manually killed the connection, for some reason didn't get logged. It did do the rollback though. I...

This is really what i am trying to do, should do following:

i) Any error on Insert - Should rollback insert , not  execute delete & log the error

I will need both, should be able to rollback and log an error if it fails. Looks like Torres solution might work, where i have begin tran wrapper under begin...