Hanging during READ_COMMITTED_SNAPSHOT ON

[pb648174]

When issuing the below command on any of our databases, it just hangs
forever.

ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.

This is a pain though and has to be done after hours. Is there a way to
issue the command while the system is in use, possibly taking just that
database offline (and not all other Dbs on the server) for a short time
and then returning it back to use using just scripting?

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

When issuing the below command on any of our databases, it just hangs
forever.

ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.

Did you use sp_who to see what other connections to the database that
were active?

You can use

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

as a guick way to get everyone out. Don't forget to set it back to
multi user when you are done.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

The problems happen intermittently, so I can't really execute the
sp_who statement when there is an error. Theoretically, how could it
ever block? Shouldn't it just get the version of the row before the
transaction started for the read operation?

[pb648174]

Whoops, wrong message, nevermind

[pb648174]

In case anyone is interested, here is the final solution we came up
with. It assumes the current DB is the one you want to set for snapshot
mania and will execute only on SQL 2005 without throwing errors on SQL
2000.

if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK
IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;
'
Exec(@sql)
end
go

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

In case anyone is interested, here is the final solution we came up
with. It assumes the current DB is the one you want to set for snapshot
mania and will execute only on SQL 2005 without throwing errors on SQL
2000.

if(charindex('Microsoft SQL Server 2005',@@version) > 0)

A somewhat simpler test:

if serverproperty('ProductVersion') not like '[1-8].%'

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

if CAST(serverproperty('ProductVersion') as varchar) not like '[1-8].%'
Erland Sommarskog wrote:

pb648174 (go****@webpaul.net) writes:

In case anyone is interested, here is the final solution we came up
with. It assumes the current DB is the one you want to set for snapshot
mania and will execute only on SQL 2005 without throwing errors on SQL
2000.

if(charindex('Microsoft SQL Server 2005',@@version) > 0)

A somewhat simpler test:

if serverproperty('ProductVersion') not like '[1-8].%'

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[sharma_vivek_us]

Hi *,
Is there any simpler way to set Isolation level means not
using the PL/SQL block.
~Vivek
--
sharma_vivek_us
------------------------------------------------------------------------
sharma_vivek_us's Profile: http://www.dbtalk.net/m441
View this thread: http://www.dbtalk.net/t309038

[Erland Sommarskog]

sharma_vivek_us (sh********************@no-mx.forums.yourdomain.com.au)
writes:

Is there any simpler way to set Isolation level means not
using the PL/SQL block.

Could you clarify what you want to achieve?

"PL/SQL block" is not something you need to use with SQL Server - or even
can. PL/SQL is Oracle or DB2.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cimode]

What's you hardware conf.? You may want to check the controller's
behavior...The versionning behind READ_COMMITTED_SNAPSHOT imposes super
heavy IO overhead on the controller...Set up a trace on Physical Disk:
Average sec per/write
Physical Disk: Average sec per/read and monitor until next
problem...Intermittent behaviors often result of a extreme disphase
between physical resources and logical need...

pb648174 wrote:

When issuing the below command on any of our databases, it just hangs
forever.

ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.

This is a pain though and has to be done after hours. Is there a way to
issue the command while the system is in use, possibly taking just that
database offline (and not all other Dbs on the server) for a short time
and then returning it back to use using just scripting?

[Cimode]

FYI
READ_COMMITED_SNAPSHOT is the early MS attempts at implementing ORACLE
Read Consistency versionning...

sharma_vivek_us wrote:

Hi *,
Is there any simpler way to set Isolation level means not
using the PL/SQL block.
~Vivek
--
sharma_vivek_us
------------------------------------------------------------------------
sharma_vivek_us's Profile: http://www.dbtalk.net/m441
View this thread: http://www.dbtalk.net/t309038