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.

1 Like

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

1 Like

P.S. Index rebuilds maybe?

You may check this.

hope! this helps.