Hanging during READ_COMMITTED_SNAPSHOT ON -- sql server (alternate)

sql server (alternate) : Hanging during READ_COMMITTED_SNAPSHOT ON

pb648174
5/24/2006 9:06:52 AM

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?

pb648174
5/25/2006 10:53:10 AM

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
5/25/2006 11:01:28 AM

Whoops, wrong message, nevermind

Erland Sommarskog
5/25/2006 2:33:03 PM

pb648174 (google@webpaul.net) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

pb648174
5/26/2006 8:10:06 AM

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
5/26/2006 9:16:41 PM

pb648174 (google@webpaul.net) writes:

[quoted text, click to view]

A somewhat simpler test:

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

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

pb648174
5/30/2006 7:18:03 AM

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

[quoted text, click to view]

Erland Sommarskog
6/20/2006 12:00:00 AM

sharma_vivek_us (sharma_vivek_us.29osdz@no-mx.forums.yourdomain.com.au)
writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

sharma_vivek_us
6/20/2006 12:21:41 AM

Hi *,
Is there any simpler way to set Isolation level means no
using the PL/SQL block.

~Vive

--
sharma_vivek_u
-----------------------------------------------------------------------
sharma_vivek_us's Profile: http://www.dbtalk.net/m44
View this thread: http://www.dbtalk.net/t30903

Cimode
6/20/2006 5:17:25 AM

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

[quoted text, click to view]

Cimode
6/20/2006 5:21:04 AM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

View Other Months
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007

home · blog · groups

Questions? Comments? Contact the dn