--方法1
WITH CTE_SID ( BSID, SID, sql_handle ) AS ( SELECT blocking_session_id , session_id , sql_handle FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 UNION ALL SELECT A.blocking_session_id , A.session_id , A.sql_handle FROM sys.dm_exec_requests A JOIN CTE_SID B ON A.SESSION_ID = B.BSID ) SELECT C.BSID , C.SID , S.login_name , S.host_name , S.status , S.cpu_time , S.memory_usage , S.last_request_start_time , S.last_request_end_time , S.logical_reads , S.row_count , q.text FROM CTE_SID C JOIN sys.dm_exec_sessions S ON C.sid = s.session_id CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q ORDER BY sid
--方法二
declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 print @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 print @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 print @@ERROR if @intCountProperties=0 select N'现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select N'引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + N'进程号,其执行的SQL语法如下' else select N'进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ N'被进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +N'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who
以下为未翻译连接查询及说明(说明 内容摘抄于https://www.cnblogs.com/MrHSR/p/9156452.html)
declare @dbName varchar(32)='master' select @dbName,a.spid,a.kpid,a.waittime,a.blocked ,a.status,hostname,[program_name],cmd,[loginame],a.open_tran,b.text,a.last_batch From [dbo].[SYSPROCESSES] a OUTER APPLY sys.dm_exec_sql_text (a.sql_handle) AS b where a.dbid=db_ID(@dbName ) and a.spid<>@@SPID order by last_batch
常用字段说明
字段名称 |
说明 |
spid 会话ID(进程ID) |
SQL内部对一个连接的编号,一般来讲,小于50,如果用户连接的编号,大于50 |
blocked 阻塞ID |
阻塞的进程ID, 值大于0表示阻塞, 值为本身进程ID表示io操作 如果blocked>0,但waittime时间很短,说明阻塞时间不长,不严重 |
waitresource 等待资源 |
格式为 fileid:pagenumber:rid 如(5:1:8235440) |
kipid 线程ID |
当kpid值为不0时,代表当前是活动用户 kpid=0, waittime=0 空闲连接 kpid>0, waittime=0 运行状态 kpid>0, waittime>0 需要等待某个资源,才能继续执行,一般会是suspended(等待io) kpid=0, waittime=0 但它还是阻塞的源头,查看open_tran>0 事务没有及时提交 |
waittime |
当前等待时间(以毫秒为单位) |
open_tran |
进程的打开事务数 |
hostname |
建立连接的客户端工作站的名称 |
program_name |
应用程序的名称 |
hostprocess |
工作站进程 ID 号 |
loginame |
登录名 |
status |
running = 会话正在运行一个或多个批 如果status 上有好几个runnable状态任务,需要认真对待。 cpu负荷过重没有及时处理用户的并发请求 |
last_batch |
(客户最后一次调用存储过程或者执行查询的时间) |
open_tran |
连接开启的事务数 |
一次kill 所有指定数据库 已经sleeping的连接
declare @db varchar(32)='master' ---游标更新删除当前数据 ---1.声明游标 declare orderNum_03_cursor cursor scroll for select a.spid,a.status From [dbo].[SYSPROCESSES] a OUTER APPLY sys.dm_exec_sql_text (a.sql_handle) AS b where a.dbid=db_ID(@db) and a.spid<>@@SPID and a.spid>50 and b.text is not null --2.打开游标 open orderNum_03_cursor --3.声明游标提取数据所要存放的变量 declare @pid int ,@status varchar(32) --4.定位游标到哪一行 fetch First from orderNum_03_cursor into @pid,@status --into的变量数量必须与游标查询结果集的列数相同 while @@fetch_status=0 --提取成功,进行下一条数据的提取操作 begin if(LTRIM(rtrim(@status))='sleeping') begin exec('kill '+@pid) end fetch next from orderNum_03_cursor into @pid,@status --移动游标 end --关闭游标 close orderNum_03_cursor --释放 DEALLOCATE orderNum_03_cursor
其它数据库维护资料
http://www.maomao365.com/?p=5464 (mssql sqlserver 数据库常用维护脚本收集)