Re: Hanging during READ_COMMITTED_SNAPSHOT ON

pb648174 (google@xxxxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Moving DB from one drive to another
    ... In the copy of Books Online I have it suggests ALTER DATABASE SET OFFLINE. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Remove ldf file
    ... SQL Server spans data across all data files within a filegroup.So all files ... REMOVE FILE in ALTER DATABASE command to drop the NDF file. ... See DBCC SHRINKFILE and ALTER database in books online for syntax. ... like to be able to delete that file and only use the *.mdf to store data. ...
    (microsoft.public.sqlserver.server)
  • Re: Second Transaction Log File
    ... What do you think is it good or bad to remove the second log file? ... Let's see, then I would have to look up the exact syntax in Books Online, ... and then ALTER DATABASE with REMOVE FILE. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: How to clear connetion to detach database?
    ... What version of SQL Server? ... ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK AFTER n SECONDS ... If this application is currently maintaining an active connection to the ... database you're trying to detach, you're going to need to switch the context ...
    (microsoft.public.sqlserver.programming)
  • Re: How to drop one of the tempdb files
    ... then ALTER DATABASE. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
Loading