determining transaction level in stroed procedures via sql trace

Question

I am using read committed transactions in my asp.net application.

I'm supsicious that somehow when I get to SQL Server read commited isn't being used.

Is there a way to determine via a SQL Trace what the isolation level of a transaction is. All I can see is BEGIN TRANSACTION

Answer 1

I am not sure if it is possible though profiler. However, you can try the following...

1. Open sql management studio
2. Issue the 'begin trans' command
3. insert/update some data to a table
4. Select the newly inserted/updated data without explicitly commiting the above trans
5. You should not see the newly inserted/updated data until you commit them
6. This proves that your using the read commited isolation level

Answer 2

One thing you can check via Profiler is the isolation level set on login. Trace the "Audit Login" event and include the TextData column; this will show a bunch o' stuff, which includes the isolation level.

I'm a bit cavalier on this, because I don't know why it shows what it does -- either this is default SQL behavior, or it depends entirely on whatever is making the connection. This won't show how it changes, though the usual statment traces would -- though sifting through thousands of events for this can be a major pain.