Our website was briefly down and we determined it was due to SQL Server not responding. I opened the Activity Monitor in SSMS and killed sessions belonging to a couple of devs.
The site then came back but now I have no idea what the were doing, or whether it was just coincidental that the site came up the same time I killed their processes. I have talked to the devs but didn't find out anything useful.
I don't suppose SQL makes any record of the query that was running in a session that's just been killed?
Best Answer
By default nothing keep tracks of all running query in SQL Server. Depending on what the query was you might explore few things. None of these guarantee that you will find what you are looking for.
sys.dm_exec_query_stats (Transact-SQL)
Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
You will need to join these with other DMVs. Link has examples.
A Few Cool Things You Can Identify Using the Default Trace by Greg Larsen
Use the system_health Session if you still have the trace files.
Since you mentioned SQL Server not responding I would also look at SQL Error log, Windows event logs.