Commit Transaction not commiting the transaction properly.

Do you have nested transactions?  Perhaps a ROLLBACK occurred somewhere along the way?  Maybe the data was legitimately deleted?  Without more info, it's hard to say.

No i am not using nested transaction, same commit transaction for many times on a same table.but using commit transaction for every run of their respective begin transaction.

Vikrant,

This is pretty hard to troubleshoot.  Have you run Profiler in an isolated environment to see what is happening?  Here are some tips on Profiler - http://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/.

Do you also have any error handling in place that could help trap the erro?  Here are some tips on error handling - http://www.mssqltips.com/sql-server-tip-category/79/error-handling/.

HTH.

Thank you,
Jeremy Kadlec

Jeremy Kadlec,

 i have not tried by profiler in an isolated environment yet. i will try and let you know.

Thanks!!

One thing you can do is , check from your session if there is any uncommited transaction?

select @@trancount

0-commited.

1-uncommitted.