In SQL Server 2008 you can use two prepared method to get the changes on a table. One is called as "Change Tracking" and the other is "Change Capturing". With change tracking option you can have the last chage about the record. By using change capturing you can have the history of the changes.

In my project i decided to use the change tracking method. Becaus it is a internal process it is faster. And i don't need to manage change version etc. So, it was good choice in my opinion.

I successfully applied the method to my system. Everything was working well unitill we need to restart our server to fix another isue. When we start the server back, we noticed that backup of the change tracking enabled databases were not working anymore. It was a bug with the chacnge tracking system. When you restart the sql server, cleanup process of the change tracking system is corrupted and some duplicate records remains in "syscommittab" system table. The message was

“Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. [SQLSTATE 23000] (Error 2601) Failed to flush the commit table to disk in dbid ID due to error 2601. Check the errorlog for more information. [SQLSTATE 42000] (Error 3999) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.”

Workaround of this case is so troublesome. This is a known isue. Check the KB 978839. I applied the service pack 3. But it did it again. At the end, i removed the change tracking implementation. As you guess, it was not easy while the system is used.

I don't suggest to use change tracking system in sql server 2008.