Yep, it’s not rocket science, but it can really help you in your everyday work. If you don’t know about this trick – you can waste a lof of time.
Most commonly I need this when I’m working on integration tests. Most of integration tests need a lot of prepared data in database. Next our tests are basing on this data and it can turns out it’s not so easy 🙂
So, common scenario:
- I’m running application in debug mode
- code hits breakpoint or I’m just stepping through the lines of code
- I want to query database to look up some data
- my query hangs
Why your query doesn’t want to execute properly?
Because your application (of course if your app hits database) has open transaction. Normally you are not able to read “dirty” data, so as long as your code is stopped in debugging mode your select query in Management studio will not execute.
How to solve this problem if you need to see what’s going on in your database?
Just change query Transaction Isolation Level to READ UNCOMMITED:
- if your query is still hanging, stop it
- go to “Query” and next to “Query options”
- select “Advanced” and change Transaction Isolation Level to READ UNCOMMITED
Now every query will execute, even if you’re debbuging your code.
One important note – as you can notice we’re changing Transaction Isolation Level in query settings, so those changes are applied only to our active query. If you open new query you will have to change this once again.