Post reply

How to get list of old scripts executed in sql server?

  • September 13, 2012 at 4:09 am

    #261438

    Hi All,

    I have requirement to get old executed queries from sql server for 10, 14, or 30 days. I got a script from the following site script also below.

    http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    ORDER BY deqs.last_execution_time DESC

    The above script is giving only today's data not any old.

    Is there any way, to get old executed scripts? Please share your thoughts.

    Thanks.

    _____________________________________________
    One ounce of practice is more important than tonnes of dreams

  • September 13, 2012 at 4:25 am

    #1537117

    Unless you had a trace in place starting 30 days ago, you cannot get this information. The DMV you used just shows queries that are still in cache, not all queries that ran.

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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