.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Snapshot Isolation Levels

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

I have a customer that is getting long blocking chains caused by reporting against OLTP data. I have successfully setup replication to report against a subset of the tables for another customer and this has worked fine. The only downside to this, besides the replication performance impact, is that if many reports are running and updates are happening at the same time, the second report (and up) will be blocked. The blocking chain is the first report blocks the replication agent which blocks the second report and so on. This is probably acceptable but I want to make sure it is the best option. Therefore, I would like to look into using either read committed snapshot isolation (RCSI) or the snapshot isolation (SI).

1) Are RCSI and SI mutually exclusive options? From my testing, I could turn on one without turning on the other. However, I read a blog that said you can only turn on SI once RCSI has been turned on for the database. Is this true? I am assuming the blog was wrong.

2) If I only turn on SI and only ever use a "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" statement in read only reports, the application (non-reports) should function the exact same correct? (Excluding the added tempdb space used). Obviously a huge dilemma with setting any of these snapshot levels is trying to prevent the changes to how the applicati

View Complete Post

More Related Resource Links

isolation mode and levels

Hi all i need tostudi the isolation mode/levels so please will you tell me where i will get help on it,also i have to implemet it so i need examples too!

Snapshot isolation transaction aborted due to update conflict.

To avoid deadlocks, we switched from ReadCommittedSnapshot isolation to SnapShot isolation for a SQL Server database at the database level and transaction level in the client code. Now, when two users perform concurrent operations on the database through the client, one of the clients get this error: "Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.cust_table' directly or indirectly in database 'cust_database' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement."What can we do to avoid deadlocks and update conflicts at the same time? (The same code with Oracle database and Oracle client works without any issues with the default Read Committed Snapshot isolation level)

Read Committed Snapshot vs Snapshot isolation


Hi all,

Can someone show me with an example what the difference betweent these two are?

please help me

Isolation Level set to Read Committed Snapshot, Deadlock Graph shows Read Committed


I must misunderstand something here. I have altered the DB to allow READ_COMMITTED_SNAPSHOT and some deadlock situations have been solved. Nevertheless, in a very similar situation, I'm getting deadlocks and the deadlock graph (XML) shows me the following:

isolationlevel="read committed (2)"

What do I miss here?



How to take a snapshot pic from a (.mp4/.3gp) video file ?


Hello there,

I need some help with video embedding in ASP.NET. So I am developing a litle custom blog and I need users to upload Quick Time videos (.mp4,.3gp) but I also need when upload completes to take a snapshot from the uploaded video which should be used as a link to a page that plays the video, in other words I need to take a frame from this video file... Do you have any idea how this is possible??

Thanks in advance

Editor's Note: Many Levels of Security


Every year at this time, we bring you our now-famous security issue. We recognize the vast importance of writing and deploying secure code-it affects so many areas of concern-which is why we devote an entire issue each year to the topic.

MSDN Magazine November 2005

Extranet/Internet Isolation


We have a requirement where we want to use MOSS platform to enable collaboration over intranet/extranet(corp users) and internet (FBA users). But our corp security team has objected to same infra being exposed to both extranet and internet domains. They are asking for physical isolation between these domains.

They feel FBA auth is not rigorous enough and might be compromized. And having both domain supported from same infra might compromize corp network as well.

One obvious option is to have 2 separate MOSS farms for each domain, and sync data between them. Are there any other solutions for this?

Request MOSS architects and experts to provide some pointers on these.

Initialize snapshot fro alternate location - multiple .bcp files for an article

I have to replicate over 50GB of data over a slow network.  I did not use the option to initialize snapshot from database backup because the replication articles contain row filters.  If I do that, I'll have to run a lot of scripts to remove the data and other unnecessary database objects on the subscriber. Instead, I created a workaround.  On the publisher, I first create the actual push subscription to the target subscriber on the publication.  This subscription is set not to initialize from snapshot.  I then created a second push subscription on the same publication, but the subscriber is a random database on the publisher server.  This second subscription is a dummy subscription set to initialize from snapshot - the purpose is to generate the necessary snapshot files.  I then reinitialized all subscriptions and generate the new snapshot files. On the subscriber, an empty database is created with the same tables as the publisher database.  I created an identical publication on this empty database, and a dummy push subscription on the target subscriber.  The subscription is reinitialized, and the snapshot files on the empty database is created.  These dummy snapshot files are then overwritten with the actual snapshot files created on the publisher, and then I synchronize the the dummy subscriptions with the actual sn

Merge Replication: How to give read access on snapshot share to a sql account

Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

Help! "You must rerun snapshot because current snapshot files are obsolete" every 90 minutes!

I'm having difficulty with SQL Server replication getting machines in the field to replicate correctly.   Here are the specifics of my installation:   100 remote tablet PCs using web sync to update. 1/2 running Vista, 1/2 XP (all latest updates applied) SQL Server Express 2005SP2 w/ cumulative update 6.   Server: Win2003 (all latest updates) SQL Server 2005SP2 CU6   There is 1 database. For each tablet I create a separate publication (PUB001, PUB002, PUB003, etc.) with filtered articles containing data for the tablet. Each publication has a single subscription -- the tablet to which the subscription is assigned. Subscriptions are set to expire after 14 days. Snapshot agent was run when subscription was created, and is set to run automatically every 14 days.     This is a "test" system which is scheduled to roll out into production in 2 weeks.   For the past month I have been running 6 tablets A-OK with no problems (had 6 separate publications/subscriptions on the server/tablets).   I use a stored procedure to create the publications and subscriptions. This stored procedure hasn't changed in months.   Yesterday, we added another 6 tablets w/ separate publications on the server, using the same aforementioned stored procedure.     The tablet application is set to auto-sync over the internet every 15 minutes.   Starting yesterday afternoon, at random, tablets would st

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

What is the default isolation level (SQL Server 2008)

Hi, My database settings are: SET ALLOW_SNAPSHOT_ISOLATION ON READ_COMMITTED_SNAPSHOT ON Does this mean I don't have to explicitly have: Set Transaction Isolation Level Read Committed for each proc, i.e. because I have the two settings at the database level there is no need set the Transaction Isolation Level in each proc or does this need to be done as well as the database level settings. Cheer, Peter  

Unable to get Lastperiods 4 periods in time hierarchy at different levels.

Hi, I am unable to get last 4 periods when the weeks are falling at two different months. Example: The calendarsales contains the following hierachy : Year, HalfYear, Quarter, Month, Week August month contains the following weeks:1033,1034,1035 July month contains the follwing weeks: 1032,1031,1030. So i am selecting the week 1035 and i need last 4 weeks till 1032. But the below qry is returning last 3 weeks 1035,1034,1033. It is unable to retrieve 1032 as it is coming under previous month of July.  Below is the mdx query i am using. **************************************** WITH     SET ORDEREDCAT AS   Order ( [Dim_Product].[ProductDefaultHierarchy].[Category]. MEMBERS ,[Measures].[Sales Units] , BDESC )     MEMBER [Measures].[SalesUnits_L_4WKBP] AS   Sum ( ( {   LastPeriods (4 ,[Dim_RetailerSalesCalendar].[CalendarSales]. CurrentMember ) } ,[Measures].[Sales Units] ) )     SELECT     NON EMPTY {[OrderedCat]} ON 1 ,{ [Measures].[SalesUnits_L_4WKBP]     } ON 0 FROM   [Aroyale] WHERE   {[Dim_Retailer].[Retailer].&[5]}* {[Dim_RetailerSalesCalendar].[CalendarSales].[week].&[1035]} ***************************** Please can any one help me on this. Regards, Srini.

Wiki site categorization and isolation

Our business folks are looking to have enterprise Wiki site to categorize based on departments. Person from one department should not be able to contribute and/or create pages for other departement. What are the ways to implement this in SP2010? We would prefer out-of-box ways since we are too small team to mainatain much customization. Thanks, Hemlata

How to let a non admin business user run a replication snapshot job?

According to the msdn library, I would have to give the non admin user (User1) SQLAgentUserRole privileges. But it also says that it has to be a local job and not a multi server job. Is the snapshot agent job a local or multi server job? Considering the snapshot agent is a local job and i make USER1 the owner of that job. He should be able to run the job rite? I need to give USER1 the ability to run sp_start_job to start the snapshot agent ONLY! (and not any other jobs). So what are the steps to do this? I also read that the only way to achieve this is by creating a proxy account? Is there an article somewhere that best describes this?  

Merge Replication, Push Subscription : The snapshot takes centuries to apply

Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend