Determine the Last Time a SQL Trigger was Used

In SQL 2008 and higher there is a new feature that can help audit triggers. By running the script below you can get some important information about triggers that are running or possibly just as important, analyzing triggers that have not ran. It can also help in understanding how long it takes a trigger to run (execution time) and how many times the trigger has executed.

SELECT
    tr.object_id,
    tr.database_id,
    DB_NAME(database_id) AS 'Name_of_Database',
    OBJECT_NAME(object_id, database_id) AS 'trigger_name',
    tr.last_execution_time,
    tr.total_elapsed_time/tr.execution_count AS [avg_time],
    tr.execution_count
FROM sys.dm_exec_trigger_stats AS tr

--If you want all of the triggers then comment out this next line
WHERE OBJECT_NAME(object_id, database_id)='Name_Of_Trigger'

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


7 + = 13

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>