SQL Stop Responding, how to find why?

  • Hello

    Ours production server has stopped responding for 10 minutes few hours ago, I was not able to check what happened in time, because was notified only when everything finished and SQL working properly now.

    Is there any way to find what happened?

    Thanks, Alex.

  • Check the SQL Server Error Logs or Windows Application or System Log to find the possible reason.

    Thank You,

    Best Regards,

    SQLBuddy

  • It seems SQL server worked ( I see records in the dm_exec_query_stats for this time) , but quries worked slow and applications generated timeout errors.

    So real question : Is it possible to find which query caused all those probs? At least is it possible to find all queries with stats which was started in the date / time range? So I will be able to analize it and find first with big CPU time and / or read / write counts?

  • To check most expensive queries by CPU time check

    http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

    Also you need to check for the extended blocking happening which could cause app timeouts. Also chek whether the server is experiencing any CPU, memory or IO bottlenecks.

    http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

    Thank You,

    Best Regards,

    SQLBuddy

  • "Most queries by CPU time" tsql not help, because in this time almost all queries work long, very very long time , It seems I need first query which started work slow. Am I correct? Is it possible?

  • Check the LAST execution time column for the queries and then match it with the time of slowdown.

    Thank You,

    Best Regards,

    SQLBuddy

  • Have you checked the default trace for what might have been executing and correlating that with the code that used a lot of resources?

  • Nothing look fishily, I will monitor SQL instance and will add more info.

    Thanks, guys.

Viewing 8 posts - 1 through 7 (of 7 total)

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