Post reply

Db Snapshots question

  • August 19, 2012 at 12:50 pm

    #260224

    Hi,

    I have a question on database snapshots.

    I have created a table and inserted 2 rows.

    create table tab7

    (c1 int primary key clustered,

    c2 int,

    c3 char(2000)

    )

    insert into tab7

    select 1,1,'aa'

    union all

    select 2,2,'bb'

    Then i have created a snapshot. it created in 2 secs.

    Then i am inserting 50000 rows into the table in a transaction. Parallely,

    begin tran

    declare @i int

    set @i = 3

    while @i <= 50000

    begin

    insert into tab7 values (@i, @i, 'a')

    set @i = @i + 1

    end

    commit tran

    go

    I opened a new session, i have initiated the create database snapshot. My observation, i see

    CREATE DATABASE testdb_snap2

    ON

    (NAME = testdb,

    FILENAME = 'C:\Snaps\snap2.ssf')

    AS SNAPSHOT OF testdb

    IO_completion waittype and snapshot creation is taking a long time, alomost my transaction( INSERT ) is finished, the snapshot is created. To be realistic a little bit before it is getting created.

    Why it is waiting for so long time. cant the snapshot create at that timeframe or point in time snapshot.

    Want to understand why it is taking such a long time?

    Thanks in Advance.

  • August 20, 2012 at 11:59 pm

    #1527942

    A database snapshot will always be transactionally consistent with its source database at the time of creation. Therefore the incomplete transaction will have to be rolled back in the database snapshot and I suspect this is why it is not immediately available.

  • August 21, 2012 at 10:27 pm

    #1528440

    Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply