read_committed_snapshot take forever to turn off

I have turned on the database option read_committed_snapshot
alter database mydb set read_committed_snapshot on
this only took 1 sec.
But now I'm trying to turn it off.
alter database mydb set read_committed_snapshot off.
It already ran about 2 hours and it is still running.
Please help.
Thanks.
Pingx
Likely, it is an open transaction. The ALTER DATABASE topic in Books Online
says "In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no
active connections to the database except for the connection executing the
ALTER DATABASE command. ".
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:823B6EB2-3417-4B32-8C1F-DD4F42A1BA58@.microsoft.com...
> Perhaps it is a refresh problem? Or perhaps you have some open transaction
> which holds the transition?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>
|||You guys are right. I killed some sessions and the command finished.
Thanks
"Pingx" wrote:

> I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>

read_committed_snapshot take forever to turn off

I have turned on the database option read_committed_snapshot
alter database mydb set read_committed_snapshot on
this only took 1 sec.
But now I'm trying to turn it off.
alter database mydb set read_committed_snapshot off.
It already ran about 2 hours and it is still running.
Please help.
Thanks.
PingxPerhaps it is a refresh problem? Or perhaps you have some open transaction which holds the
transition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>|||Likely, it is an open transaction. The ALTER DATABASE topic in Books Online
says "In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no
active connections to the database except for the connection executing the
ALTER DATABASE command. ".
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:823B6EB2-3417-4B32-8C1F-DD4F42A1BA58@.microsoft.com...
> Perhaps it is a refresh problem? Or perhaps you have some open transaction
> which holds the transition?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>>I have turned on the database option read_committed_snapshot
>> alter database mydb set read_committed_snapshot on
>> this only took 1 sec.
>> But now I'm trying to turn it off.
>> alter database mydb set read_committed_snapshot off.
>> It already ran about 2 hours and it is still running.
>> Please help.
>> Thanks.
>> Pingx
>>
>>
>|||You guys are right. I killed some sessions and the command finished.
Thanks
"Pingx" wrote:
> I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>

read_committed_snapshot on

set transaction isolation level read committed
alter database mydatabase set read_committed_snapshot on
From the Sql Server docs :
"Row versioning is used to present each statement within the transaction
with a transactionally consistent snapshot of the data as it existed at the
start of the statement. Locks are not used to protet the data from updates
by other transactions."
But given that read queries can have complex execution plans, how can the
server know the set of rows that need to be versioned without blocking all
other transactions and figuring it out ( which would be the same as using
shared locks ) ?
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) ?
When you set a database to READ_COMMITTED_SNAPSHOT, all row changes are
versioned independently of select queries. Select queries in the
READ_COMMITTED isolation level use the latest row versions available at the
time the SELECT statement started.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"John A Grandy" <johnagrandy@.gmail.com> wrote in message
news:eU1pJxfjIHA.5504@.TK2MSFTNGP05.phx.gbl...
> set transaction isolation level read committed
> alter database mydatabase set read_committed_snapshot on
> From the Sql Server docs :
> "Row versioning is used to present each statement within the transaction
> with a transactionally consistent snapshot of the data as it existed at
> the start of the statement. Locks are not used to protet the data from
> updates by other transactions."
>
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) ?
>
>

read_committed_snapshot on

set transaction isolation level read committed
alter database mydatabase set read_committed_snapshot on
From the Sql Server docs :
"Row versioning is used to present each statement within the transaction
with a transactionally consistent snapshot of the data as it existed at the
start of the statement. Locks are not used to protet the data from updates
by other transactions."
But given that read queries can have complex execution plans, how can the
server know the set of rows that need to be versioned without blocking all
other transactions and figuring it out ( which would be the same as using
shared locks ) '> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) '
When you set a database to READ_COMMITTED_SNAPSHOT, all row changes are
versioned independently of select queries. Select queries in the
READ_COMMITTED isolation level use the latest row versions available at the
time the SELECT statement started.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"John A Grandy" <johnagrandy@.gmail.com> wrote in message
news:eU1pJxfjIHA.5504@.TK2MSFTNGP05.phx.gbl...
> set transaction isolation level read committed
> alter database mydatabase set read_committed_snapshot on
> From the Sql Server docs :
> "Row versioning is used to present each statement within the transaction
> with a transactionally consistent snapshot of the data as it existed at
> the start of the statement. Locks are not used to protet the data from
> updates by other transactions."
>
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) '
>
>

READ_COMMITTED_SNAPSHOT

READ_COMMITTED_SNAPSHOT in 2005 is what I wanted.

However, I am still using 2000. READ_COMMITTED in 2000 is different.

Can anyone give me advice on this issue?

(or there is no way in Sql Server 2000 to handle the following scenario:

Process One

UPDATE tableA SET fieldB = 'testing' WHERE fieldA = 1;

Process Two

SELECT * FROM tableA WHERE fieldA = 1;

While Process One is working, Process Two will fail. But I just want to allow Process Two to read the data (i.e. the version that before Process One is working). This is what READ_COMMITTED_SNAPSHOT do in Sql Server 2005.

)

Thanks for any advice.

Hi Wilson. There is no automatic method to allow this type of read in Sql 2000. You can make use of the read uncommitted isolation level, which will give you dirty reads (in your scenario above process two would read a value of 'testing' for fieldB where fieldA = 1). Of course, using the read committed isolation level in Sql 2000, if the transaction isn't long running, you should still be able to read the data successfully...perhaps we could help you tune the schema a bit to allow for faster read/write access and shorter transaction time instead?

HTH,

Chad

|||

Thanks for your confirmation.

Better to upgrade to Sql Server 2005.

Thanks again.

sql

Read_committed_snapshot

Hello All,

I have another problem with setting READ_COMMITTED_SNAPSHOT on SQL Server 2005 now.

The problem is:L
In SQL Server 2005, when running following statement:

ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON

It takes so long time to fnish. Actually I have been waiting 25 minutes for it, it is still not yet finished.

Do you know why?

Thanks a lot

MelFrom BOL,
When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.

SO close other connections in that database.|||Thanks Mallier :)

Do you know how to use that "termination" option like:

ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
WITH <termination>

I still have not yet figured out how to use that.|||ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE|||Again,In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command



ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE -- or ucan specify the time in seconds|||Thanks Mallier, I am trying it now ..

READ_COMMITTED_SNAPSHOT

ALTER DATABASE TestDb SET READ_COMMITTED_SNAPSHOT ON;

The above statement work fine in SQL Server 2005.

But my problem is that I have some old databases still using Sql Server 2000. I know the above statement is only valid in 2005.

Is there any workaround in Sql Server 2000 to do the same thing?

Thanks.

You can use READ_COMMITTEE isolation in SQL 2000 and this READ_COMMITTED_SNAPSHOT is introducted in SQL 2005.

http://www.informit.com/articles/article.asp?p=27020

http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

|||What you want is non-blocking read-committed isolation level in SQL2000. No, it is not supported. Only choice you have is to use read-uncommitted but that will potentially require you to change your app. Also, your app will need to be changed to handle uncommitted data.|||

Hello Wilson,

Is it the "Row versioning" functionnality that you want?

This is something new on SQL Server 2005.

Regards.

Carl

READ_COMMITTED_SNAPSHOT

ALTER DATABASE TestDb SET READ_COMMITTED_SNAPSHOT ON;

The above statement work fine in SQL Server 2005.

But my problem is that I have some old databases still using Sql Server 2000. I know the above statement is only valid in 2005.

Is there any workaround in Sql Server 2000 to do the same thing?

Thanks.

You can use READ_COMMITTEE isolation in SQL 2000 and this READ_COMMITTED_SNAPSHOT is introducted in SQL 2005.

http://www.informit.com/articles/article.asp?p=27020

http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

|||What you want is non-blocking read-committed isolation level in SQL2000. No, it is not supported. Only choice you have is to use read-uncommitted but that will potentially require you to change your app. Also, your app will need to be changed to handle uncommitted data.|||

Hello Wilson,

Is it the "Row versioning" functionnality that you want?

This is something new on SQL Server 2005.

Regards.

Carl

READ_COMMITTED_SNAPSHOT

Hi Sql Server experts,
I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
I'd like to know what impact this will have to the database.
Thanks for your help.
Pingx
Did you try Google?
http://technet.microsoft.com/en-us/library/ms173763.aspx
http://msdn2.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:6C06B5CC-02B1-47AE-9ECE-5A6B7639AC3F@.microsoft.com...
> Hi Sql Server experts,
> I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
> I'd like to know what impact this will have to the database.
> Thanks for your help.
> Pingx
|||You best have some spiffy I/O backing up an appropriately sized/tuned
tempdb.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:6C06B5CC-02B1-47AE-9ECE-5A6B7639AC3F@.microsoft.com...
> Hi Sql Server experts,
> I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
> I'd like to know what impact this will have to the database.
> Thanks for your help.
> Pingx
|||Thanks. This is very helpful.
"TheSQLGuru" wrote:

> You best have some spiffy I/O backing up an appropriately sized/tuned
> tempdb.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:6C06B5CC-02B1-47AE-9ECE-5A6B7639AC3F@.microsoft.com...
>
>

READ_COMMITTED_SNAPSHOT

Hi Sql Server experts,
I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
I'd like to know what impact this will have to the database.
Thanks for your help.
PingxDid you try Google?
http://technet.microsoft.com/en-us/library/ms173763.aspx
http://msdn2.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:6C06B5CC-02B1-47AE-9ECE-5A6B7639AC3F@.microsoft.com...
> Hi Sql Server experts,
> I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
> I'd like to know what impact this will have to the database.
> Thanks for your help.
> Pingx|||You best have some spiffy I/O backing up an appropriately sized/tuned
tempdb.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:6C06B5CC-02B1-47AE-9ECE-5A6B7639AC3F@.microsoft.com...
> Hi Sql Server experts,
> I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
> I'd like to know what impact this will have to the database.
> Thanks for your help.
> Pingx|||Thanks. This is very helpful.
"TheSQLGuru" wrote:
> You best have some spiffy I/O backing up an appropriately sized/tuned
> tempdb.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:6C06B5CC-02B1-47AE-9ECE-5A6B7639AC3F@.microsoft.com...
> > Hi Sql Server experts,
> >
> > I'm going to set the option READ_COMMITTED_SNAPSHOT to ON.
> > I'd like to know what impact this will have to the database.
> >
> > Thanks for your help.
> >
> > Pingx
>
>sql