Post reply

How to see the last execution date of database objects in sql server 2005

  • May 24, 2012 at 1:00 am

    #256857

    Q: How to see the last execution date of database objects in sql server 2005 ????

    I used

    select t.name

    ,last_user_seek

    ,last_user_scan

    ,last_user_lookup

    ,last_user_update

    from sys.dm_db_index_usage_stats i JOIN

    sys.tables t ON (t.object_id = i.object_id)

    where database_id = db_id() and t.is_ms_shipped = 0

    order by t.name

    But I am able to get only tables last accessed.

    I want SP's, Functions, Views etc,, Respectively.

    Can somebody Help.....????

    Have some better way to find it.

  • May 24, 2012 at 1:04 am

    #1492275

    Even I tried,,

    SELECT qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,

    qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid,st.text

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp

    WHERE st.text like '%AllReqMailGet%'

    I am getting ERROR:

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.dm_exec_text_query_plan'.

    Have some script to find the details ???

    Please post ASAP..

    Thanks in advanced

  • May 24, 2012 at 2:37 am

    #1492315

    There's no script that will get you that information. You can look in the plan cache (sys.dm_exec_query_stats/sys.dm_exec_cached_plans) and some objects will have an entry there. For those that don't, there's no way to tell when they were last used without some custom monitoring.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 24, 2012 at 8:41 am

    #1492602

    Thanks,

    How can I identify the Objects from Plan_handle from (sys.dm_exec_query_stats/sys.dm_exec_cached_plans)

  • May 24, 2012 at 1:16 pm

    #1492756

    This may help:

    SELECT TOP 10

    DB_NAME(x.dbid) AS db,

    OBJECT_SCHEMA_NAME(x.objectid, x.dbid) AS schemaname,

    OBJECT_NAME(x.objectid, x.dbid) AS objectname,

    LEFT(x.text, 100),

    p.*

    FROM sys.dm_exec_cached_plans p

    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) x;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply