Find out why SQL server was having issues between timestamp

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?
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Yes! check the SQL Server error logs and the Windows Event Logs to see if any errors were logged prior to/during the outage.
1
websssAuthor Commented:
nothing in the logs around this time
0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Oh, that would mean some intermittent network issue caused the connections to reset.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
websss, a feedback will be appreciated.
Cheers
0
websssAuthor 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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
websssAuthor Commented:
Thanks
The problem is i'm often sleeping when the issue happens!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always enable the SQL Server Audit if you don't use any monitoring tool like SCOM or BMC Patrol.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
websss, were you able to solve this issue?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.