read_committed_snapshot take forever to turn off

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pingx - 18 Mar 2008 21:34 GMT

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

Reply to this Message

Tibor Karaszi - 18 Mar 2008 21:43 GMT

Perhaps it is a refresh problem? Or perhaps you have some open transaction which holds the
transition?

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>I have turned on the database option read_committed_snapshot
>
[quoted text clipped - 11 lines]
>
> Pingx

Reply to this Message

Gail Erickson [MS] - 18 Mar 2008 22:01 GMT

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. ".

Signature

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

> Perhaps it is a refresh problem? Or perhaps you have some open transaction
> which holds the transition?
[quoted text clipped - 14 lines]
>>
>> Pingx

Reply to this Message

Pingx - 18 Mar 2008 23:14 GMT

You guys are right. I killed some sessions and the command finished.

Thanks

> I have turned on the database option read_committed_snapshot
>
[quoted text clipped - 11 lines]
>
> Pingx

Reply to this Message