Post reply

Please provide t-sql script SQL queries Executed from last 24 hours with Data and time stamp

  • May 6, 2016 at 5:50 am

    #310346

    Hi Please suggest t-sql script executed from Last 24 hours with queries start time and Query execution date & time

    t-sql script SQL queries Executed from last 24 hours with Data and time stamp

    Thanks in Advance

  • May 6, 2016 at 6:26 am

    #1877095

    Unless you had some custom monitoring, you're not going to get that.

    You can query the plan cache (sys,dm_exec_query_stats cross apply sys.dm_exec_sql_text) to get the text and last execution time of distinct procedures/ad-hoc batches, assuming they still have plans in cache, but that's the best you're going to get. SQL does not store anywhere the actual data that was returned by each query. It can't, the memory/storage requirements would be prohibitive.

    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 6, 2016 at 6:27 am

    #1877096

    unless you have an extended event or audit already in place, you cannot capture that data; SQL executes a query and throws it away, but it might save the plan(the fast way it determined to get the data).

    you can get an idea of that small piece of it by looking at what is in the dmv's and the plan cache, dsomething like this is just one example:

    SELECT

    fn.*,

    st.*

    FROM sys.dm_exec_query_stats st

    CROSS APPLY sys.dm_exec_sql_text(st.[sql_handle]) fn

    if you are looking for whodunnit info (Did Lowell query the Payroll database?!?! did he access something he shouldn't?) that information is not captured, again, unless you put something in place to capture it.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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