[sql server] 取得SQL Server當前連線數、死結與踢除會話細節（SQL Issues )

• 刪除SQL當前正處於睡眠（Sleeping）狀態的連線

DECLARE @user_spid INT

DECLARE @iHours INT
SET @iHours = 1  -- DELETE thread sleeping for N hours

DECLARE curspid CURSOR fast_forward FOR
  SELECT spid
  FROM   master.dbo.sysprocesses (nolock)
  WHERE  spid > 50  -- avoid system threads
         AND status = 'sleeping'  -- only sleeping threads
         AND Datediff(hour, last_batch, Getdate()) >= @iHours
         AND spid <> @@spid  -- ignore current spid

OPEN curspid
FETCH next FROM curspid INTO @user_spid

WHILE (@@FETCH_STATUS = 0)
  BEGIN
      PRINT 'Killing ' + CONVERT(VARCHAR, @user_spid)
      EXEC('KILL ' + @user_spid)
      FETCH next FROM curspid INTO @user_spid
  END

CLOSE curspid
DEALLOCATE curspid

查詢當前的連線細節與總連線數（SQL Connection Session）

USE master
--查詢Session總量；查詢當前連線數
SELECT cntr_value AS User_Connections FROM sys.sysperfinfo AS sp
WHERE sp.object_name = 'SQLServer:General Statistics'
AND sp.counter_name = 'User Connections'
GO

--查詢Session細節；查詢當前連線細節，包含SessionId、登入時間、連線時間、連線位址、登入帳號、連線狀態...
SELECT c.session_id, c.connect_time, s.login_time, c.client_net_address, s.login_name, s.status ,s.host_name,s.program_name
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id
ORDER BY s.login_time DESC
GO

查詢資料庫死結細節

SELECT 
  L.request_session_id AS SPID, 
  DB_NAME(L.resource_database_id) AS DatabaseName, 
  O.Name AS LockedObjectName, 
  P.object_id AS LockedObjectId, 
  L.resource_type AS LockedResource, 
  L.request_mode AS LockType, 
  ST.text AS SqlStatementText, 
  ES.login_name AS LoginName, 
  ES.host_name AS HostName, 
  TST.is_user_transaction as IsUserTransaction, 
  AT.name as TransactionName, 
  CN.auth_scheme as AuthenticationMethod, 
  L.request_status AS RequestStatus 
FROM 
  sys.dm_tran_locks L 
  INNER JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id 
  INNER JOIN sys.objects O ON O.object_id = P.object_id 
  INNER JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id 
  INNER JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id 
  INNER JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id 
  INNER JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST 
WHERE 
  resource_type <> 'DATABASE' 
ORDER BY 
  L.request_session_id

強制移除某個Session，將其剔除

sp_lock
dbcc inputbuffer(YourSessionID)
kill YourSessionID

EXEC('KILL YourSessionID')
EXEC('KILL YourSessionID')
EXEC('KILL YourSessionID')
...

參照： https://slashview.com/archive2014/20140128.html