Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    24

    Unanswered: 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

    Mel

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    From 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.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    24
    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.

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    ALTERDATABASE bugdb2
    SET READ_COMMITTED_SNAPSHOT ON
    WITH
    ROLLBACK IMMEDIATE
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Posts
    269
    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

    Code:
     
    ALTER  DATABASE bugdb2
    SET READ_COMMITTED_SNAPSHOT ON
    WITH  ROLLBACK IMMEDIATE -- or ucan specify the time in seconds
    
    Last edited by mallier; 02-01-06 at 11:05.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Sep 2003
    Posts
    24
    Thanks Mallier, I am trying it now ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •