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?
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.
websssCEOAuthor Commented:
nothing in the logs around this time
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Oh, that would mean some intermittent network issue caused the connections to reset.
Determine the Perfect Price for Your 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 with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
websss, a feedback will be appreciated.
Cheers
websssCEOAuthor 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 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.
websssCEOAuthor Commented:
Thanks
The problem is i'm often sleeping when the issue happens!
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.

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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
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.