Change database/Transaction Isolation level

_________________________________________________________________

By default sql server database isolation level is set to READCOMMITED. There are some time when we don’t want that. We want to lower the isolation level. Even though we understand the it will lead us to read dirty page as well as phantom data.

DBCC useroptions

Go


SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED

    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]


This changes are at the transaction level. You can not change this at the database level. Instead, you can use this:


ALTER DATABASE  Blogs
     SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

ALTER DATABASE Blogs
     SET READ_COMMITTED_SNAPSHOT ON;


GO
DBCC useroptions



You might experience blocking while setting the Read Committed Snapshot. If you can, kill all the process then apply this change. OR Do this

-- If this doesn’t allow you then do this.
ALTER DATABASE Blogs
    SET READ_COMMITTED_SNAPSHOT ON
    WITH ROLLBACK AFTER 5 ;