How to Troubleshoot DB Blocking and Connection issues
Recently we faced SQL Server 2008 DB resource blocking and connection issues where application server was running out of available pool of DB connections. To tackle this problem we tried following approach-
1. Find all active sessions established with SQL Server
2. Find the number of connections established from each server/ client and total number of active connections
3. Find processes which were getting blocked for resources.
Following queries help in finding this information-
Print (‘=======To find the list of active sessions with details==============’)
GO
SELECT spid, DB_NAME(sp.dbid) dbname, cpu, physical_io, waittime, program_name, hostname, login_time, last_batch, blocked, loginame, text, status, cmd
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
where spid > 49
and status <> ‘sleeping’
Go
Print (‘=======To Find the no. of connections established from each server/ client and total number of active connections==============’)
Go
select hostname, COUNT(*) total_conn_count, SUM(case when status <> ‘sleeping’ then 1 else 0 end) total_active_conn_count
from sys.sysprocesses
where spid > 49
group by hostname
Go
Print (‘=======To find out blockings==============’)
Go
select * from sys.sysprocesses
where blocked > 0
Go
Later we scheduled these queries as job to run at specific time and captured the activities and connection detail. Below screen shot shows how to schedule a DB job-
1. Login to SQL Server and Start a new Job. Provide Job name and description as below-
2. Create Job step as shown below. Copy paste the queries provided in the article and set the DB name against which job will run-
3. In Advanced step provide actions in case of success or failure along with path to save the job output-
4. Setup schedule for the job-
