SQL server 2008 R2 Database Isolation Change

SQL Server Administration
1

I want to change my database isolation to read uncommitted in SQL server 2008 R2, Can someone tell me the exact process and how should I can achieve this?

2

Not sure how to do that. However, do you really want read uncommitted? Might I suggest if there is a case where it is needed put the isolation group in a stored procedure. The command within a stored procedure is:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3

You cannot change the isolation level as a database default. If you could, you sure would not want to default to read uncommitted. You' be guaranteed dirty reads and chaotic results.

4

You can achieve the same effect as READ UNCOMMITTED by setting the DB to READ ONLY.
(ie No locks will be set or honored.)

I can think of only two cases where you would want to use READ UNCOMMITTED.

  1. You are absolutely certain the data is read only while the query is running and are very keen to conserve resources.
  2. You need to run a quick interactive query on a production database and are more concerned about locks than the accuracy of the results.

I suspect you may need to read about READ COMMITTED SNAPSHOT ISOLATION.

1 Like
5

Moved topic to SQL Server Administration.

6

Thanks to all for your reply