1

Topic: Snapshot isolation transaction failed accessing database ' tempdb'

People prompt.
In basis with included  there is a procedure. In it there is a quantity @tables.
in this procedure there is such error:
Snapshot isolation transaction failed accessing database ' tempdb ' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation
I while in details did not dig, decided to ask, someone can faced and directs me to the necessary channel.
I while clarified that the similar error can be received in such cases:

use tempdb;
CREATE TABLE T (id int);
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
select * from T;

-------------

 use tempdb; - or the user basis where it is included 
CREATE TABLE ##A (id int);
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
select * from ##A; 

With  variables @table such error does not arise...

print @@ version
Microsoft SQL Server 2008 (SP1) - 10.0.2766.0 (X64)
Feb 25 2010 0:51:37 PM
Copyright (c) 1988-2008 MICROSOFTS CORPORATION
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

2

Re: Snapshot isolation transaction failed accessing database ' tempdb'

"In basis with included " = "in basis with included  and SNAPSHOT ISOLATION"

3

Re: Snapshot isolation transaction failed accessing database ' tempdb'

wrote:

With  variables @table such error does not arise...

Well so they . And in a remaining case the server to you it is perfect  says that isolation level SNAPSHOT cannot be used for objects in tempdb, both it is explicit, and it is implicit (through temporary tables) for for tempdb  it is impossible to include option ALLOW_SNAPSHOT_ISOLATION. I.e. you can use isolation level SNAPSHOT if for all bases which objects you use, the appropriate option is included.

4

Re: Snapshot isolation transaction failed accessing database ' tempdb'

//http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d901e9de-9fa3-44b6-a54c-23699e3c8969/

5

Re: Snapshot isolation transaction failed accessing database ' tempdb'

pkarklin wrote:

Well so they . And in a remaining case the server to you it is perfect  says that isolation level SNAPSHOT cannot be used for objects in tempdb, both it is explicit, and it is implicit (through temporary tables) for for tempdb  it is impossible to include option ALLOW_SNAPSHOT_ISOLATION. I.e. you can use isolation level SNAPSHOT if for all bases which objects you use, the appropriate option is included.

I am all perfectly I understand. But procedure does not use any  tempdb except tabular variables. In sense - there is not present ##tables, constant tempdb tables. The resulted scripts - that as I could generally receive this error, but anything similar is not used. At us half of developers at all in course about ##tables, and  it is dynamic forbidden to create constants.
The most ridiculous the error arose 1 time. Developers caught in the broad gulls, swear, what exactly that method which causes this procedure just and produced an error. To me to play back  it was not possible... .

6

Re: Snapshot isolation transaction failed accessing database ' tempdb'

tpg wrote:

//http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d901e9de-9fa3-44b6-a54c-23699e3c8969/

This link read still before  a question. It is absolutely useless.
Whether the dude asks "there is a risk of switching-on  insulation for tempdb" - that basically is impossible.
And the aunty answers how to measure the size of copies in mbytes... I hope only that these two each other understood. It did not give me anything.

7

Re: Snapshot isolation transaction failed accessing database ' tempdb'

In general, I will tell it that let at first , and we will understand then.