August 11, 2015 at 10:58 pm
Please I would like to know how to log how many times a trigger fires.
August 12, 2015 at 12:19 am
deebabat (8/11/2015)
Please I would like to know how to log how many times a trigger fires.
Why? I ask because it may help figure out the best way for you.
--Jeff Moden
August 12, 2015 at 6:16 am
Am trying to keep track of some triggers in a new database I just inherited in order to troubleshoot some issues, I would like to know the start time, end time, and types of trigger.
August 12, 2015 at 7:22 am
deebabat (8/12/2015)
Am trying to keep track of some triggers in a new database I just inherited in order to troubleshoot some issues, I would like to know the start time, end time, and types of trigger.
Are you saying you want to audit the start and end times for each trigger when they execute? How many triggers did you inherit? Not totally sure what you mean by "types" of trigger. Do you mean insert, update and delete? Can't you determine that from the name or do these triggers handle multiple types of DML statements?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2015 at 8:51 am
There a couple of things that pop into my head:
1. Modify the triggers to insert into a table when they fire. Obviously adds overhead to each trigger firing.
2. Server Side trace using the SP:Completed event with filters on ObjectType = 21076 and DatabaseName/DatabaseID. This will catch every trigger that fires in the specified database(s). Should have minimal impact.
3. Extended Events Session using the module_end event with filters on database_name/database_id and object_type = TR. This will catch every trigger that fires in the specified database(s). With even less impact than a Server Side trace. This works from 2008 onwards. You can even do more by using the histogram (asynchronous_bucketizer prior to 2012) target which will just record how many times each trigger is fired.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2015 at 9:02 am
This will give you some info on the triggers & aggregated execution stats.
If you need individual executions then I think you'll need to look at using extended events/server side trace/profiler (in order of preference)
OBJECTPROPERTY can also give you more info, e.g. first & last triggers for each type, if they've been set.
select SCHEMA_NAME(o.schema_id) AS [Schema], o.name as [Table], t.name as [Trigger], t.is_instead_of_trigger, OBJECTPROPERTY (t.object_id, 'ExecIsInsertTrigger') AS [Insert],
OBJECTPROPERTY (t.object_id, 'ExecIsUpdateTrigger') AS [Update], OBJECTPROPERTY (t.object_id, 'ExecIsDeleteTrigger') AS [Delete], ts.cached_time, ts.last_execution_time, ts.execution_count,
ts.total_elapsed_time/ts.execution_count AS AvElapsed, ts.total_worker_time/ts.execution_count AS AvCPU, ts.total_logical_reads/ts.execution_count AS AvLogicalReads,
ts.total_physical_reads/ts.execution_count AS AvPhysicalReads, ts.total_logical_writes/ts.execution_count AS AvWrites
from sys.triggers t
INNER JOIN sys.objects o on o.object_id = t.parent_id
LEFT JOIN sys.dm_exec_trigger_stats ts ON ts.object_id = t.object_id
order by SCHEMA_NAME(o.schema_id), o.name, t.name
August 12, 2015 at 9:09 am
Gazareth (8/12/2015)
This will give you some info on the triggers & aggregated execution stats.
Bear in mind that the exec_trigger_stats DMV depends on the trigger's plan being in cache. If the plan is removed from cache, the stats are discarded. If the trigger runs again, the stats start again from 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply