检测死锁:
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
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
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=0begin
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
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=0begin
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)
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.id 10 JOIN syscolumns d ON b.id = d.id 11 AND b.colid = d.colid 12 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 ,