locked
CDC RRS feed

  • Question

  • I have a history table which has data from year 1990. I am planning to archive the last 20 years of data. But for archiving I need to collect stats on the data which are still accessed by the end users. Please let me know how to track it. I know we can use Change data capture (CDC) which is used to track of the DML tasks. I am only after select statement which are against the table.

    Any suggestions/help ??


    -kccrga http://dbatrend.blogspot.com.au/


    • Edited by kccrga Thursday, April 17, 2014 2:25 AM
    Thursday, April 17, 2014 2:13 AM

Answers

  • Hi kccrga,

    Change Data Capture(CDC) records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational 'change tables'. you can chooses to track these change tables contain columns that reflect the column structure of the source table. However, if you want to track the time and the end user stats on the data which are still accessed, CDC will not implement  your requirement.  I recommend you use SQL Server Profiler to trace all of T-SQL and user login events that are performed in your SQL Server instance. For more information, see: http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Thursday, April 24, 2014 7:43 AM
    Friday, April 18, 2014 6:32 AM

All replies

  • Refer the below link

    http://blogs.msdn.com/b/sreekarm/archive/2009/01/05/auditing-select-statements-in-sql-server-2008.aspx

    --Prashanth

    Thursday, April 17, 2014 8:57 PM
  • Hi kccrga,

    Change Data Capture(CDC) records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational 'change tables'. you can chooses to track these change tables contain columns that reflect the column structure of the source table. However, if you want to track the time and the end user stats on the data which are still accessed, CDC will not implement  your requirement.  I recommend you use SQL Server Profiler to trace all of T-SQL and user login events that are performed in your SQL Server instance. For more information, see: http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Thursday, April 24, 2014 7:43 AM
    Friday, April 18, 2014 6:32 AM