Sql-server – Change tracking SQL Server 2008

sql-server-2008

My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table.

Due to some specific reasons I have to use change tracking as I was told not to use triggers and Change Data Capture.

Right now I am able to fetch current data and which column is modified. But in addition I want to track time of modification and old data.

But if anyone can help me to get any one of these two will be very helpful for me.

Best Answer

If you can't add any extra storage for the data (as noted in your comment) then you simply can't do this. SQL Server doesn't track the old and modified rows for you.

There's a duct-tape way of doing it temporarily: you can read the transaction log. When combined with the full chain of backups, you can build the old & new data together. Third party data recovery products like Quest LiteSpeed do this - for any transaction, they'll show you the old and new data by reading through the full backup and transaction log backups.

Related Solutions

Sql-server – How to detect any changes to a database (DDL and DML)

One idea would be to make a snapshot every day and monitor the snapshot file size on the disk using a file monitor. The snapshot is increasing its size only when data is added there, so it would be a valid idea if you would find a tool to monitor the real size (reported size).

Now.. I didn't use this, so can't give you technical insights :-).

Another idea would be to verify the transaction log of each db (if you're using full recovery mode on them, of course) with some function I've seen on the forums (db_fnlog.. or something) that reads operations from the log, and see if you have any deletes/inserts/updates.

Those are no easy things to do.. but I hope you'll find them useful.

PS: found the article with the log read function (it's fndblog, by the way :-): Read the transaction log by Jens K. Suessmeyer.

Sql-server – How to know which stored procedure or trigger is using a table on SQL Server 2008 R2

Search all code using sys.sql_modules

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules sm
WHERE definition LIKE '%Mytable%'

Or use Red Gate SQL Search which is completely free

Do not use syscomments or INFORMATION_SCHEMA.RUBBISH

Best Answer

Related Solutions

Sql-server – How to detect any changes to a database (DDL and DML)

Sql-server – How to know which stored procedure or trigger is using a table on SQL Server 2008 R2

Related Question