We help IT Professionals succeed at work.

Find out why SQL server was having issues between timestamp

websss
websss asked
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

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
Oh, that would mean some intermittent network issue caused the connections to reset.
Vitor MontalvãoIT 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ãoIT 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ãoIT 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!
IT 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ãoIT Engineer
Distinguished Expert 2017

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

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