检测死锁：
View Code 1 declare @spid int, 2 @bl int, 3 @intTransactionCountOnEntry int, 4 @intRowcount int, 5 @intCountProperties int, 6 @intCounter int 7 create table #tmp_lock_who 8 ( id int identity( 1, 1), 9 spid smallint, 10 bl smallint 11 ) 12 IF @@ERROR <> 0 13 print @@ERROR 14 insert into #tmp_lock_who(spid,bl) 15 select 0 ,blocked from ( select * from sysprocesses where blocked > 0 ) a 16 where not exists( select * from ( select * from sysprocesses where blocked > 0 ) b where a.blocked =spid) 17 union 18 select spid,blocked from sysprocesses where blocked > 0 19 IF @@ERROR <> 0 20 print @@ERROR 21 -- 找到临时表的记录数 22 select @intCountProperties = Count( *), @intCounter = 1 from #tmp_lock_who 23 IF @@ERROR <> 0 24 print @@ERROR 25 if @intCountProperties = 0 26 select ' 现在没有阻塞和死锁信息 ' as message 27 -- 循环开始 28 while @intCounter <= @intCountProperties 29 begin 30 -- 取第一条记录 31 select @spid = spid, @bl = bl from #tmp_lock_who 32 where Id = @intCounter 33 begin 34 if @spid = 0 35 select ' 引起数据库死锁的是: ' + CAST( @bl AS VARCHAR( 10)) + ' 进程号,其执行的SQL语法如下 ' 36 else 37 select ' 进程号SPID： ' + CAST( @spid AS VARCHAR( 10)) + ' 被 ' + ' 进程号SPID： ' + CAST( @bl AS VARCHAR( 10)) + ' 阻塞,其当前进程执行的SQL语法如下 ' DBCC INPUTBUFFER ( @bl ) end 38 -- 循环指针下移 39 set @intCounter = @intCounter + 1 40 end 41 drop table #tmp_lock_who
杀死死锁和进程
View Code 1 declare @sql nvarchar( 500) 2 declare @spid nvarchar( 20) 3 declare #tb cursor for select spid = cast(spid as varchar( 20)) from master..sysprocesses -- where dbid=db_id(@dbname) 4 open #tb 5 fetch next from #tb into @spid 6 while @@fetch_status = 0 7 begin 8 exec( ' kill ' + @spid) 9 fetch next from #tb into @spid 10 end 11 close #tb 12 deallocate #tb
查看锁进程
View Code 1 create table #t 2 (req_spid int, 3 obj_name sysname) 4 declare @s nvarchar( 4000), 5 @rid int, 6 @dbname sysname, 7 @id int, 8 @objname sysname 9 declare tb cursor for 10 select distinct req_spid,dbname = db_name(rsc_dbid),rsc_objid 11 from master..syslockinfo where rsc_type in( 4, 5) 12 open tb 13 fetch next from tb into @rid, @dbname, @id 14 while @@fetch_status =0 begin 15 set @s = ' select @objname=name from [ ' + @dbname + ' ]..sysobjects where id=@id ' 16 exec sp_executesql @s,N ' @objname sysname out,@id int ', @objname out, @id 17 insert into #t values( @rid, @objname) 18 fetch next from tb into @rid, @dbname, @id 19 end 20 close tb 21 deallocate tb 22 select 进程id =a.req_spid 23 ,数据库 = db_name(rsc_dbid) 24 ,类型 = case rsc_type when 1 then ' NULL 资源（未使用） ' 25 when 2 then ' 数据库 ' 26 when 3 then ' 文件 ' 27 when 4 then ' 索引 ' 28 when 5 then ' 表 ' 29 when 6 then ' 页 ' 30 when 7 then ' 键 ' 31 when 8 then ' 扩展盘区 ' 32 when 9 then ' RID（行 ID) ' 33 when 10 then ' 应用程序 ' 34 end 35 ,对象id =rsc_objid 36 ,对象名 =b.obj_name 37 ,rsc_indid from master..syslockinfo a left join #t b on a.req_spid =b.req_spid 38 go 39 drop table #t
查看阻塞信息
View Code 1 SELECT SPID =p.spid, 2 3 DBName = convert( CHAR( 20),d.name), 4 5 ProgramName = program_name, 6 7 LoginName = convert( CHAR( 20),l.name), 8 9 HostName = convert( CHAR( 20),hostname), 10 11 Status = p.status, 12 13 BlockedBy = p.blocked, 14 15 LoginTime = login_time, 16 17 QUERY = CAST( TEXT AS VARCHAR( MAX)) 18 19 FROM MASTER.dbo.sysprocesses p 20 21 INNER JOIN MASTER.dbo.sysdatabases d 22 23 ON p.dbid = d.dbid 24 25 INNER JOIN MASTER.dbo.syslogins l 26 27 ON p.sid = l.sid 28 29 CROSS APPLY sys.dm_exec_sql_text(sql_handle) 30 31 WHERE p.blocked = 0 32 and d.name = ' 数据库名字 ' 33 34 AND EXISTS ( SELECT 1 35 36 FROM MASTER..sysprocesses p1 37 38 WHERE p1.blocked = p.spid)
枚举索引
1 SELECT 索引名称 = a.name , 2 表名 = c.name , 3 索引字段名 = d.name , 4 索引字段位置 = d.colid, 5 c.crdate 6 FROM sysindexes a 7 JOIN sysindexkeys b ON a.id = b.id 8 AND a.indid = b.indid 9 JOIN sysobjects c ON b.id = c.id10 JOIN syscolumns d ON b.id = d.id11 AND b.colid = d.colid12 WHERE a.indid NOT IN ( 0, 255 ) 13 -- and c.xtype='U' and c.status>0 --查所有用户表 14 --AND c.name = 'ORDER' --查指定表 15 ORDER BY 16 c.crdate desc,17 c.name ,18 a.name ,