Where to view server logs for cases when SQL Server cannot provide concurrent connections

Our SQL Server DB is load-heavy and there are certain instances where the server cannot provide more connections to applications. We are currently suffering from logouts and there are suspicions that these logouts are caused by insufficient concurrent connections. Where can I find logs for such incidents in SQL Server? Is it also possible to capture them using server audit logs?

Solution 1:

You need to investigate live with dmv queries to know why is your SQL Server instance unnable to offer new connections.

First the DMV to read are essentially :

1. sys.dm_exec_requests

2. sys.dm_exec_sessions

3. sys.dm_exec_connections

Combine to the table functions :

4. sys.dm_exec_sql_text

5. sys.dm_exec_query_plan

And also :

6. sys.dm_tran_active_transactions

7. sys.dm_tran_session_transactions

8. sys.dm_tran_locks

To do so, you need to access the server via the SQLcmd.exe tool in mode DAC (-A in the command line).

Usually it is because of some "spaghetti" locks that is due to underindexing or a bad control of transaction...