Post reply

Change Data Capture , Change value in [msdb].[dbo].[cdc_jobs]

  • September 28, 2012 at 10:57 am

    #393279

    Hi Team,

    I am working setting up CDC for a table on a database and i understand that we need to change the value of Retention column. So, if you chagne this value is this going to be server level. All the CDC enabled databases and tables are going to read the values from this value ?

    My requirement is to get notified when anybody changes a table(Insert/update/delete) with who changed and what changed. I am looking into 4 options where i couldnt able to conclude as each one has limitations or downsides.

    1) Triggers (We dont want conventional triggers)

    2) Change Tracking (No information on "Who Changed the record")

    3) CDC (Uses Agent, No information on "Who chagned", I dont know how to get notified via DBmail or something immediately after somebody changed the table)

    4) Audits ( How to get notified immediately after changes to table, T-sql reads from Audit file but is there any T-sql to read from Audit that is written in Application Log ?)

    Please advise.

    Thanks

    kmg

  • September 28, 2012 at 11:08 am

    #1543551

    Why not you can try with a job, which can capture the last modified date on the table and to to send emails?

  • September 28, 2012 at 11:15 am

    #1543554

    Hi bala, thanks for the reply.

    Sorry, Onething i forgot. I dont want a job which will be keep on monitoring the tables (CDC tables or Audit Tables). There are already tons of jobs on the server and i dont want to create and maintain another job that too which needs to be executed daily n number of times for each minute or so ... I am looking for some kind of build in funcationality to do this MONITORING JOB and kicks of a SPROC ( i can write whaterver i want here)

    Thanks,

    Jyodhu.

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

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