Can I see Historical Queries run on a SQL Server database?

  • Someone was running a query on our SQL Server database remotely and their system crashed.

    They have no backup of that query and want to see what was run on the server.

    Is it possible to find this query in a log or in a history somewhere?

    For future design you might want to consider adding triggers and audit/history tables. Then would would be able to utilize last updated time/user.

  • Correct answer

    9 years ago

    Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here: Oh **********!

    EDIT

    To make this a bit more detail of an answer, the referenced linked above Grant provides a query to simply go to the cache on the instance to pull out the query you had just executed (or atleast attempt to):

    SELECT  dest.text
    FROM    sys.dm_exec_query_stats AS deqs
            CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    WHERE   deqs.last_execution_time > '5/19/2011 11:00'
            AND dest.text LIKE 'WITH%';
    

    A few more options that were noted in the comments of Grant's blog:

    That's a nice article! Thanks! And following Grant's article, Recover Backed-up query files in SQL Server Management Studio might be helpful.

    This answer could be imported if it stated what versions of sqlserver it works on. I get this error when I try to run it: Incorrect syntax near '.'. on 2008.

    Could be imported? @MichaelPotter Copy and paste between browser and other tools will commonly change quote marks and other text, I have no control over that part.

    Sorry, please correct my question... s/imported/improved/

  • 2005+, default trace to the rescue.

    The default trace rolls over at 20mb but SQL retains the history of 5 traces. With access to the server you could retrieve the *.trc files from the MSSQL\Log directory. If you can't access the server, the following will give you the name of the current default trace file:

    SELECT * FROM ::fn_trace_getinfo(default) 
    

    If the current file is for example E:\MSSQL.1\MSSQL\LOG\log_200.trc, the previous files should be log_199.trc, log_198.trc etc. Get the contents of the trace with:

    SELECT * FROM fn_trace_gettable('E:\MSSQL.1\MSSQL\LOG\log_199.trc', default)
    
  • You might be able to retrieve info from cached query plans, check BOL for info on sys.dm_exec_query_stats, or run this from management studio connected to the same database:

    SELECT  d.plan_handle ,
            d.sql_handle ,
            e.text
    
    FROM    sys.dm_exec_query_stats d
            CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e
    

    Filter the output with

    WHERE text like '%something%'
    

    to narrow the results.

  • If database was in full recovery mode then there might be a chance to recover some data and get insights on what was done by reading transaction log.

    Unfortunately this is not supported by default but there are ways to do this.

    You can try using third party tools such as ApexSQL Log or SQL Log Rescue (free but SQL 2000 only).

    Another option is to try using undocumented functions DBCC LOG or fn_dblog. This is more complex but its free.

  • If your database is set to full recovery model you can investigate your transaction log backups. See fn_dump_dblog for more informations.

  • ApexSQL has a 'Executed queries' functionality which lets you search and filter by date.

    I am not sure if it pulls the history from the SSMS cache or actually keeps track of it by itself. You could try to install it and hope for the best.

    That's only for stuff you directly execute in query windows, and you must turn on saving.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM