SQLTeam.com | Weblogs | Forums

Trigger fire and database change history



Is there a way to track trigger fire history or which tables are changing (DML statements) on a daily basis?

Currently trying to troubleshoot an offsite backup issue where the database is changing 18-20% a day. The database is rather large and the backups are not completing in the proper time-frame. Trying to find out where all of the database change is occurring. Database size is not growing, there is just a high percentage of change per day.

Tried doing some joins on sys.tables, syscomments, sys.triggers, but there are no timestamps relevant to what I am looking for.


You'd have to add code to any trigger(s) to see details of the UPDATE activity. That is, if you want a history of what triggers fired and when, the triggers themselves need to write that out.


Compare the databases in yesterday's backup and today's to see which rows, in which tables, have changed?

Of course one row may change many times, but I suppose that is only going to give "size" to the Log backup, not (in most cases) the Data backup


P.S. Index rebuilds maybe?


You may check this.

hope! this helps.