0

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?

2 Answers 2

1

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.

1

The answer by SqlWorldWide is good for future reference, but my guess is one of your developers had caused blocking, meaning that nothing could be changed on one or more tables because of a long-running transaction that was locking a resource.

Things that cause blocking include index rebuilds, a long-running UPDATE, a big SELECT ... INTO, and so on.

In future, along with the great resources in the other answer, you can make use of sp_WhoIsActive (get it from http://whoisactive.com) to look at the blocking chain, and see which query is causing blocking.

From there, you can then kill that particular query yourself, or ask the developer to cancel their query, which avoids going into Task Manager and using a bigger hammer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.