Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
malachi151
Posting Yak Master
152 Posts |
Posted - 2013-02-01 : 13:29:10
|
I can see how to identify what has changed using Change Tracking, but is there a way to determine the date of the change?Basically, all I really need is a date and time of of the most recent change to a record. I had a trigger on the table that did this, but I wanted to replace it with Change Tracking thinking it would have better performance.Also, my prior trigger checked to make sure that something has actually changed changed on the record prior to updating the time stamp by using a checksum on the row, which made it slower, but insured that if some massive update was run on all records, but didn't actually change any data then the time stamp wouldn't be updated.I was hoping I'd be able to do this with Change Tracking as well...--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-02-01 : 17:21:05
|
Looks like this functionality is available through "Change Data Capture". Although it doesn't appear to care if a value was updated to the same value - it is transaction based and will capture all transactional changes. Looks like with CDC you can compare values though. I haven't used it yet but looks pretty robust at first glance.Be One with the OptimizerTG |
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2013-02-06 : 14:28:23
|
So, is there a way to determine date of last change with Change Tracking?--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-06 : 15:03:22
|
If my memory serves me, the LSN only tracks the order/sequence of change not the date/time of the change. |
|
|
Passer_by
Starting Member
1 Post |
Posted - 2013-03-18 : 14:33:13
|
Check the sys.dm_tran_commit_table's commit_time column. You can join the sys_change_version returned from the CHANGETABLE(CHANGES...) function to the sys.dm_tran_commit_table.commit_ts column.SELECT ct.*, tct.commit_timeFROM CHANGETABLE(CHANGES <your table here>, <last_sync_version>) CT, sys.dm_tran_commit_table tctwhere ct.sys_change_version = tct.commit_tsgo |
|
|
terredean111
Starting Member
3 Posts |
Posted - 2014-02-11 : 23:21:35
|
Hi malachi151,I have been doing this from a long time. I am in the same tracking company. You can take a look at our s/w or any other help required I will be happy to help you.Thankshttp://www.labortimetracker.com/features.cfm |
|
|
denialparl
Starting Member
5 Posts |
Posted - 2016-01-11 : 23:50:55
|
As suggested above, "Change Data Capture" functionality can resolve your concern you are looking for.In case if you wish to get the report at more granular level, you may look into Lepide SQL server auditing tool i.e, http://www.lepide.com/lepideauditor/sql-server.html that helps to track every critical changes/access and provides the report into real time with every changes value.[url][/url] |
|
|
|
|
|
|
|