locked
CDC operation giving wrong result RRS feed

  • Question

  • Hello,

    We enabled CDC on a table. When I am updating any column of existing records from the captured_column_list I am getting __$operation as 1 (delete) and 2(insert) instead of 3 and 4. What could be a possible reason for this abnormal behavior?

    Friday, May 13, 2016 12:17 AM

Answers

  • This isn't "abnormal" behaviour. Sometimes SQLS needs to delete and insert. See https://support.microsoft.com/en-us/kb/238254 for an example.

    Richard

    Friday, May 13, 2016 3:38 AM
  • Hi GV008,

    The above KB article also applies to SQL Server 2012.

    In SQL Server, under certain circumstances, a UPDATE statement is implemented as a deferred update, which is issued as a pair of DELETE and INSERT operations. Therefore, the above CDC behavior is normal in SQL Server.

    You can review the following similar threads to get details about that what happens in the database engine during the update operation.

    http://stackoverflow.com/questions/29784420/does-sql-update-do-a-delete-then-insert

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5bd0c18-ac1b-4a8a-ac22-a7ac9cc36d72/update-delete-insert?forum=transactsql

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, May 16, 2016 7:18 AM
  • Do you know how is it supposed to behave for "Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)"?

    Hi GV008,

    to be honest; I would expect that you read an article AND...

    try to reproduce the given scenario. If you would have done it this way you would see, that this behavior is not limited to the version of SQL Server but this is a default behavior of SQL Server engine. Please follow the example in my post to see how it works:

    -- Insert a few records!
    INSERT INTO dbo.foo (col1, col2, col3)
    VALUES
    (1, 1, 'Uwe Ricken'),
    (2, 1, 'Richard Lees');
    GO
    
    CHECKPOINT;
    GO

    The first step creates a table dbo.foo with a primary key (UNIQUE) on col1. No other indexes are set. Than a few records will be inserted. Please note that the values for col1 have to be unique. The CHECKPOINT only makes sure that no entries are in the log file (demo database is running in SIMPLE recovery mode!).

    Now I run an update (like the example in the linked article of Microsoft) as follows:

    UPDATE	dbo.foo
    SET		col1 = 3
    WHERE	col3 = 'Uwe Ricken';
    GO
    
    SELECT * FROM sys.fn_dblog(NULL, NULL);
    GO

    The second command takes a look into the transaction log and the - sequential - actions taken from the previous command.

    When you look to the log entries you will see that Richard is absolutely correct with his assumption. The statements have the following reasons:

    5: The table is a heap that's the reason for the HEAP-(Table)-Update. But the table has a UNIQUE index.

    6. A row will be DELETED from the INDEX [LOP_DELETE_ROWS]. The operation MARK_AS_GHOST is for the periodical check for deleted records from a background process. You can get deeper insights to that topic from an article of Paul Randal here (http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/)

    7. When a record will be deleted from a HEAP the PFS has to be updated. The PFS stores the filling grad of data pages when it is a heap. I've written an article about the allocation of pages in a heap here: (http://social.technet.microsoft.com/wiki/contents/articles/21877.sql-server-how-does-sql-server-allocate-space-in-a-heap.aspx)

    8. When the record has been deleted it has to be inserted again because of the new order. This is documented in the Line 8 with an LOP_INSERT_ROWS in the UNIQUE index. Now we have a DELETE and an INSERT produced with an UPDATE statement

    9. Here the transaction is finishing with a COMMIT

    Keep in mind that due to an INSERT the PFS has to be updated again. Now a new record has been inserted into it and the filling grade has to be set to the correct percentage value.

    So please allow me to mark the answer from Richard as CORRECT again.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Monday, May 16, 2016 9:28 AM

All replies

  • This isn't "abnormal" behaviour. Sometimes SQLS needs to delete and insert. See https://support.microsoft.com/en-us/kb/238254 for an example.

    Richard

    Friday, May 13, 2016 3:38 AM
  • Hi RichardLees and Uwe Ricken,


    Thanks for your reply! Here are the version details of my database

    Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
            Jul 22 2014 15:26:36
            Copyright(C)   Microsoft Corporation
            Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


    Based on the KB article, the functionality applies to below versions only.

    Microsoft SQL Server 2000 Standard Edition
    Microsoft SQL Server 7.0 Standard Edition
    Microsoft SQL Server 2005 Workgroup Edition
    Microsoft SQL Server 2005 Standard Edition
    Microsoft SQL Server 2005 Developer Edition
    Microsoft SQL Server 2005 Enterprise Edition

    Do you know how is it supposed to behave for "Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)"?

    Thanks!

    Friday, May 13, 2016 8:25 PM
  • Hi GV008,

    The above KB article also applies to SQL Server 2012.

    In SQL Server, under certain circumstances, a UPDATE statement is implemented as a deferred update, which is issued as a pair of DELETE and INSERT operations. Therefore, the above CDC behavior is normal in SQL Server.

    You can review the following similar threads to get details about that what happens in the database engine during the update operation.

    http://stackoverflow.com/questions/29784420/does-sql-update-do-a-delete-then-insert

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5bd0c18-ac1b-4a8a-ac22-a7ac9cc36d72/update-delete-insert?forum=transactsql

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, May 16, 2016 7:18 AM
  • Do you know how is it supposed to behave for "Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)"?

    Hi GV008,

    to be honest; I would expect that you read an article AND...

    try to reproduce the given scenario. If you would have done it this way you would see, that this behavior is not limited to the version of SQL Server but this is a default behavior of SQL Server engine. Please follow the example in my post to see how it works:

    -- Insert a few records!
    INSERT INTO dbo.foo (col1, col2, col3)
    VALUES
    (1, 1, 'Uwe Ricken'),
    (2, 1, 'Richard Lees');
    GO
    
    CHECKPOINT;
    GO

    The first step creates a table dbo.foo with a primary key (UNIQUE) on col1. No other indexes are set. Than a few records will be inserted. Please note that the values for col1 have to be unique. The CHECKPOINT only makes sure that no entries are in the log file (demo database is running in SIMPLE recovery mode!).

    Now I run an update (like the example in the linked article of Microsoft) as follows:

    UPDATE	dbo.foo
    SET		col1 = 3
    WHERE	col3 = 'Uwe Ricken';
    GO
    
    SELECT * FROM sys.fn_dblog(NULL, NULL);
    GO

    The second command takes a look into the transaction log and the - sequential - actions taken from the previous command.

    When you look to the log entries you will see that Richard is absolutely correct with his assumption. The statements have the following reasons:

    5: The table is a heap that's the reason for the HEAP-(Table)-Update. But the table has a UNIQUE index.

    6. A row will be DELETED from the INDEX [LOP_DELETE_ROWS]. The operation MARK_AS_GHOST is for the periodical check for deleted records from a background process. You can get deeper insights to that topic from an article of Paul Randal here (http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/)

    7. When a record will be deleted from a HEAP the PFS has to be updated. The PFS stores the filling grad of data pages when it is a heap. I've written an article about the allocation of pages in a heap here: (http://social.technet.microsoft.com/wiki/contents/articles/21877.sql-server-how-does-sql-server-allocate-space-in-a-heap.aspx)

    8. When the record has been deleted it has to be inserted again because of the new order. This is documented in the Line 8 with an LOP_INSERT_ROWS in the UNIQUE index. Now we have a DELETE and an INSERT produced with an UPDATE statement

    9. Here the transaction is finishing with a COMMIT

    Keep in mind that due to an INSERT the PFS has to be updated again. Now a new record has been inserted into it and the filling grade has to be set to the correct percentage value.

    So please allow me to mark the answer from Richard as CORRECT again.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)


    Monday, May 16, 2016 9:28 AM