SSTS Blog

Some news and tidbits that we share

Enabling snapshot isolation in SQL Server

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Oct 09 in Blog 0 Comments

Snapshot Isolation allows for readers to access data without blocking writers. This is more like how Oracle works and can improve overall throughput of your system. Keep in mind that snapshot isolation will place greater demands on tempdb as before images of data will be stored there in order to support this functionality.

 

With that said, this is the process to enable snapshot isolation on a database:

ALTER DATABASE <my_db> SET allow_snapshot_isolation ON
ALTER DATABASE
<my_db> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE
<my_db> SET read_committed_snapshot ON
ALTER DATABASE
<my_db> SET MULTI_USER


To verify:
SELECT is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state FROM sys.databases WHERE name='
<my_db>'

Tags: Untagged
Hits: 7035

About the author

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Comments

Please login first in order for you to submit comments