Home / server / Questions / 316054
In Process
Sam
Asked: 2011-09-28 09:36:07 +0800 CST

Unable to alter a user database

  • 772

Trying to alter a database to enable snapshot_read_committed. The statement is not running due to the CHECKPOINT process being in the database.

How can I kick the processes into another db so I can run this command?

Thanks

sql-server-2008

1 Answers

  1. What ended up working was this...

    In another database, run this statement - it's not necessary to run the full million rows.

    CREATE TABLE PageCheck
    (
      c1 INT IDENTITY ,
      c2 CHAR(5000) NOT NULL
    )

DECLARE @i INT
SET @i = 1
BEGIN TRAN
WHILE @i <= 1000000 
    BEGIN
        IF @i % 100 = 0 
            BEGIN
                COMMIT TRAN
                BEGIN TRAN
            END
        INSERT  PageCheck
                ( c2 )
        VALUES  ( '  ' )
        SET @i = @i + 1
    END
COMMIT TRAN




USE OTHER_DB
GO
CHECKPOINT
GO


USE MYDB
ALTER DATABASE MYDB
SET read_committed_snapshot ON
GO

    I had tried running the checkpoint in another database, but there wasn't any work to be done, so the process didn't switch.

    • 1