SQL Stop Responding, how to find why?

Question

Post reply

SQL Stop Responding, how to find why?

onixsoft

Mr or Mrs. 500

Points: 544
More actions
September 2, 2010 at 11:55 am

#223441

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.

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

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

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

Thank You,

Best Regards,

SQLBuddy

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 2

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?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

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

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 4

"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?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

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

Thank You,

Best Regards,

SQLBuddy

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

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

onixsoft Mr or Mrs. 500 Points: 544 More actions · Answer 7

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

Thanks, guys.