use master go declare@spidint,@blint DECLARE s_cur CURSORFOR select0 ,blocked from (select*from sysprocesses where blocked>0 ) a wherenotexists(select*from (select*from sysprocesses where blocked>0 ) b where a.blocked=spid) unionselect spid,blocked from sysprocesses where blocked>0 OPEN s_cur FETCHNEXTFROM s_cur INTO@spid,@bl WHILE@@FETCH_STATUS=0 begin if@spid=0 select'引起数据库死锁的是: '+CAST(@blASVARCHAR(10)) +'进程号,其执行的SQL语法如下' else select'进程号SPID:'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID:'+CAST(@blASVARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) FETCHNEXTFROM s_cur INTO@spid,@bl end CLOSE s_cur DEALLOCATE s_cur
ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_lockinfo]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[p_lockinfo] GO createproc p_lockinfo @kill_lock_spidbit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示 @show_spid_if_nolockbit=1--如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示 as declare@countint,@snvarchar(1000),@iint select id=identity(int,1,1),标志, 进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid, 数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu, 登陆时间=login_time,打开事务数=open_tran, 进程状态=status, 工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess, 域名=nt_domain,网卡地址=net_address into #t from( select 标志='死锁的进程', spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=a.spid,s2=0 from master..sysprocesses a join ( select blocked from master..sysprocesses groupby blocked )b on a.spid=b.blocked where a.blocked=0 unionall select'|_牺牲品_>', spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=blocked,s2=1 from master..sysprocesses a where blocked<>0 )a orderby s1,s2 select@count=@@rowcount,@i=1 if@count=0and@show_spid_if_nolock=1 begin insert #t select 标志='正常的进程', spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses set@count=@@rowcount end if@count>0 begin createtable #t1(id intidentity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255)) if@kill_lock_spid=1 begin declare@spidvarchar(10),@标志varchar(10) while@i<=@count begin select@spid=进程ID,@标志=标志 from #t where id=@i insert #t1 exec('dbcc inputbuffer('+@spid+')') if@标志='死锁的进程'exec('kill '+@spid) set@i=@i+1 end end else while@i<=@count begin select@s='dbcc inputbuffer('+cast(进程ID asvarchar)+')'from #t where id=@i insert #t1 exec(@s) set@i=@i+1 end select a.*,进程的SQL语句=b.EventInfo from #t a join #t1 b on a.id=b.id end go exec p_lockinfo
SQL --查看锁信息 createtable #t(req_spid int,obj_name sysname) declare@snvarchar(4000) ,@ridint,@dbname sysname,@idint,@objname sysname declare tb cursorfor selectdistinct req_spid,dbname=db_name(rsc_dbid),rsc_objid from master..syslockinfo where rsc_type in(4,5) open tb fetchnextfrom tb into@rid,@dbname,@id while@@fetch_status=0 begin set@s='select @objname=name from ['+@dbname+']..sysobjects where id=@id' exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id insertinto #t values(@rid,@objname) fetchnextfrom tb into@rid,@dbname,@id end close tb deallocate tb select 进程id=a.req_spid ,数据库=db_name(rsc_dbid) ,类型=case rsc_type when1then'NULL 资源(未使用)' when2then'数据库' when3then'文件' when4then'索引' when5then'表' when6then'页' when7then'键' when8then'扩展盘区' when9then'RID(行 ID)' when10then'应用程序' end ,对象id=rsc_objid ,对象名=b.obj_name ,rsc_indid from master..syslockinfo a leftjoin #t b on a.req_spid=b.req_spid go droptable #t