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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change Tracking & last modified date

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 Optimizer
TG
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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_time
FROM CHANGETABLE(CHANGES <your table here>, <last_sync_version>) CT, sys.dm_tran_commit_table tct
where ct.sys_change_version = tct.commit_ts
go
Go to Top of Page

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.
Thanks
http://www.labortimetracker.com/features.cfm
Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -