Find out why SQL server was having issues between timestamp

websss
websss used Ask the Experts™
on
Our 2014 sql server ent had issues for 5 mins yesterday

All the apps that use the db had entries in the log files at same time with similar results along lines of
"Connection Timeout Expired."

1. Is there anything I can do to find out what was happening between these times to see where issue was?

2. And is there a way to automatically write sp_whoisactive logs when I get this issue in future?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nakul VachhrajaniTechnical Architect, Capgemini India
Commented:
Yes! check the SQL Server error logs and the Windows Event Logs to see if any errors were logged prior to/during the outage.
websssCEO

Author

Commented:
nothing in the logs around this time
Nakul VachhrajaniTechnical Architect, Capgemini India
Commented:
Oh, that would mean some intermittent network issue caused the connections to reset.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
I will guess that or your SQL Server instance went offline for 5 minutes or it was your network that went down for 5 minutes.
Another option but not so easily traceable is you had some SQL Server process that had 100% of resources usage, not leaving any available resource to be used by the other processes.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
websss, a feedback will be appreciated.
Cheers
websssCEO

Author

Commented:
Thanks

I've seen it happening again but not so often

I thin there is locking and blocking occuring
But I have no idea how I find out what sql sp was blocking the rest

Any suggestions?
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
Just run the sp_who stored procedure and you'll get a list of all processes and their status. You can then identify the blocking and blocked processes.
websssCEO

Author

Commented:
Thanks
The problem is i'm often sleeping when the issue happens!
MSSQL Senior Engineer
Distinguished Expert 2017
Commented:
You can always enable the SQL Server Audit if you don't use any monitoring tool like SCOM or BMC Patrol.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
websss, were you able to solve this issue?
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comments as solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial