How To #7 – View history of executed queries

Do you find yourself in the situation where you want to audit or check what is happening with your server in terms of queries? Or maybe you are in a more unpleasant situation and you need to recover a query after a SQL Server crash or a server crash. Either way, the following script will come in handy.

The focus word here is “ran”, meaning that you can recover only queries that ran at least once. Most often situation that I get myself into is when developing stored procedures: I comment out the header part (with CREATE PROCEDURE) and I write my code checking the result from time to time. It doesn’t happen too often, but I saw it happening a few times – SSMS crashes and I didn’t get the chance to save my script or compile the procedure. And no, I don’t want to start over. 🙁

So here’s how to do it to recover you last ran queries:

 

One thought on “How To #7 – View history of executed queries

  1. Anonymous says:

    SSMS crash and SQL Server crash are not the same. This DMV is cleared after SQL server crash, so your query will not show anything before the crash. Only way to recover the last SQL from a crash either from ERRORLOG or use SqlDumper to grab a dump on crash

Leave a Reply

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

seventeen − 12 =