>Or, is it possible once we get a database in this situation to view any open/pending transactions?

DBCC OPENTRAN() will give you the oldest active transaction in the database. This will give you the connection that created the transaction but the command that did.

>Can someone tell me if it is possible to log each BEGIN/COMMIT/ROLLBACK and what called/caused it?

Use Profiler and log the SPStart and SPComplete events plus the SQLTransaction event. Configure the trace to store the captured events to a table. Then you should be able to trace each start transaction back to the SPStart on the same connection.

Not perfect but it should get you what you need.

-Mike

>Hi All,
>
>It seems that we may have a proc in our app that is not closing a transaction. We haven't been able to determine a certain set of steps/data to duplicate it.
>
>I tried to set up Profiler to log to disk. I added SQL Transactions to the trace, but it doesn't tell me if it is logging a START/STOP/ROLLBACK or where it came from?
>
>Can someone tell me if it is possible to log each BEGIN/COMMIT/ROLLBACK and what called/caused it?
>
>Or, is it possible once we get a database in this situation to view any open/pending transactions? I know we can look at locks, but will that tell me what SPROC created the lock? What should I look at?
>
>Any other suggestions to narrow this down?
>
>Thanks,
>BOb