The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
1answer
73 views

READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem: USE [sor] GO ALTER DATABASE [sor] SET ...
2
votes
0answers
120 views

Why does a simple select query produce a deadlock?

I am trying to analyze randomly occurring deadlock problems on an SQL Server 2008 R2 database. The deadlocks occur in very rare cases when SSRS tries to access one of 5 different stored procedures. ...
5
votes
1answer
331 views

Why are U locks required when using Read Committed Snapshot Isolation

I consider myself a beginner at Sql Server locking. My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From ...
7
votes
1answer
539 views

TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?

I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE. Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = ...
8
votes
1answer
395 views

In case of multiple updates of the same record in a single transaction, how many versions are being stored?

According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation "... All the earlier versions of a particular record are chained in a linked ...
7
votes
3answers
260 views

“When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained”?

The MSDN online article "Snapshot Isolation in SQL Server" states: "An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, ...
3
votes
1answer
104 views

Why do row-versioning isolations use a linked list of versions of record? [closed]

Trying to better understand row-versioning based transaction isolations in SQL Server... According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation": ...
6
votes
1answer
92 views

Does RCSI use less or same space than SNAPSHOT? Why?

Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server... MSDN online article "Choosing Row Versioning-based ...
2
votes
1answer
56 views

Why - read but not update ops fail in case of lacking space in tempdb database?

MSDN online article "Choosing Row Versioning-based Isolation Levels" states: "tempdb must have enough disk space for the version store. If there are very long-running transactions, all the ...
0
votes
0answers
119 views

What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012. After reading app's documentation as well as feedbacks on internet from those who already accomplished ...
2
votes
0answers
197 views

Read-Commited Snaphsot isolation causing deadlocks?

Our DBA has some type of process setup to monitor deadlocks and sends out an emails when a deadlocks occurs. We have been testing read committed snapshot isolation on our application for a couple ...
4
votes
1answer
156 views

Distributed transaction and Read Committed Snapshot

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
4
votes
1answer
452 views

Locking in “Read Committed Snapshot”

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending eg: update table1 set col1 = col1 + 1 where PKcol < 3 update table1 set col1 = col1 + 1 ...
2
votes
1answer
74 views

ALLOW_SNAPSHOT_ISOLATION Duration

I would like to enable ALLOW_SNAPSHOT_ISOLATION on a production database that has ~400 million total records. I understand that 14 bytes will need to be added to each record. If I set ...
3
votes
1answer
377 views

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI. How can I be seeing ...
4
votes
1answer
445 views

Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases?

Why is the database property Allow Snapshot Isolation set to TRUE on the master and msdb databases, but FALSE on other system databases? The default option for this property is also FALSE when ...