Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 snapshot isolation level usage
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

17 Posts

Posted - 02/05/2014 :  12:20:30  Show Profile  Reply with Quote
Dear All,

There are some transaction tables in which more than one user add and update records (only).

what ever they add and update in transaction tables, based on that entry they add a record in Table A1

, Table A1 has two cols one keeps the table name of transaction table and other col keeps the pk(primarykey) of transaction tables.

So Table A1 always gets only inserts,

Table A1 gets entry only for transaction tables , and only when transaction table gets entry .

At the same time there is a process (ts) which reads Table A1 on time basis, picks up all records

form Table A1 and reads data from transaction tables on the basis of PK stored in it . there it after inserts all the read records into a new temp table.

and at the end of transaction it deletes records from Table A1.

after some time it again picks up new records from Table A1 and repeats the process.

For process (ts) . i want to use ALLOW_SNAPSHOT_ISOLATION so that user can keep on entering records.

Q1) The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before one can start a transaction that uses the SNAPSHOT isolation level. I wanted to know should i set the option to OFF after the process(ts) is complete, and switch it on again on the database when process(ts) starts again.

that is, keeping it on all the time will affect the database in any case?

Q2) ALLOW_SNAPSHOT_ISOLATION ON , will affect other isolation level's transactions or only to snapshot isolation levels transactions. that is, i have old stored proc and front end applications like web or window on .net which are using default isolation levels.

Q3) is my choice of isolation level for process(ts) is correct or there can be any other solution.

Note: "the information is quite less but i wont be able to give full information."

yours sincerely

  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000