怎样获得死锁的进程的内容? 1:sp_lock2:Select * from master..sysprocesses where blocked <> 0 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 sp_lock报告有关锁的信息。下面的示例显示 SQL Server 中当前持有的所有锁的信息。USE masterEXEC sp_lock 马克理解错误!!!是死锁进程所执行的那句SQL语法!要知道是哪个操作引起死锁,并做出判断来处理它!楼上就差的更远了。 马克说的是对,但是我不知道怎么获得SQL语句的内容,请指教 SQL语句的内容,在企业管理器中是可以看到,不知怎样用查询分析器中查出,关注。 企业管理器->服务器->管理->当前活动->锁/进程ID->双击ID即可 手工kill掉,我没有其它办法,呵呵,一下 dbcc inputbuffer(spid)其中 spid是进程号 用sp_who可以返回当前进程。。用 sp_MSget_current_activity 51,2返回发生死锁进程列表,至于判断发生死锁自己写过程。。 对了,再说明:其中的51代表当前查询的spid,M$是用sp_MSset_current_activity null 取得。 楼上没看清是吧?死锁的SPID在座的大概都能得到。我要的是该SPID的SQL内容,在脚本里得到,而不是管理器里。 sp_MSset_current_activity这个在MSSQL2000帮助里查不到。请告诉具体参数以及意义 sp_MSset_current_activity代码:ps:我还没来得及研究 有需要的就看看/*****Begin of No.340,WITH Name: sp_MSset_current_activity********/-- =============================================-- sp_MSset_current_activity-- =============================================create procedure dbo.sp_MSset_current_activity @id int OUTPUTasset transaction isolation level read uncommittedset quoted_identifier onset nocount onset lock_timeout 5000declare @stmt as nvarchar(4000)-- =============================================-- make tables SPID depended-- =============================================declare @locktab as sysnamedeclare @proctab as sysnamedeclare @locktb2 as sysnamedeclare @proctb2 as sysnameset @id = @@spidset @locktab = N'##lockinfo' + rtrim(convert(nvarchar(5), @id))set @proctab = N'##procinfo' + rtrim(convert(nvarchar(5), @id))set @locktb2 = N'tempdb..##lockinfo' + rtrim(convert(nvarchar(5), @id))set @proctb2 = N'tempdb..##procinfo' + rtrim(convert(nvarchar(5), @id))-- =============================================-- delete temp tables-- =============================================if (object_id(@locktb2) is not null) exec(N'drop table ' + @locktab)if (object_id(@proctb2) is not null) exec(N'drop table ' + @proctab)-- =============================================-- lockinfo table-- =============================================set @stmt =N'select [Process ID] = l.req_spid, [DBID] = l.rsc_dbid, [Database] = db_name(l.rsc_dbid), [ObjID] = l.rsc_objid, [Object] = convert(nvarchar(386), ''''), [Table] = convert(sysname, ''''), [ObjOwner] = convert(sysname, ''''), [IdxID] = l.rsc_indid, [Index] = convert(sysname, ''''), [Lock Type] = (select substring (v.name, 1, 4) from master.dbo.spt_values v where l.rsc_type = v.number and v.type = ''LR''), [Mode] = (select substring (u.name, 1, 8) from master.dbo.spt_values u where l.req_mode + 1 = u.number and u.type = ''L''), [Status] = (select substring (x.name, 1, 5) from master.dbo.spt_values x where l.req_status = x.number and x.type = ''LS''), [Owner] = (select substring (o.name, 1, 8) from master.dbo.spt_values o where l.req_ownertype = o.number and o.type = ''LO''), [Resource] = substring (rsc_text, 1, 16)into ' + @locktab + ' from master.dbo.syslockinfo l with (NOLOCK) order by l.req_spid'exec (@stmt)-- =============================================-- processinfo table-- =============================================set @stmt =N'select [Process ID] = p.spid, [User] = case when p.spid > 6 then convert(sysname, ISNULL(suser_sname(p.sid), rtrim(p.nt_domain) + ''\'' + rtrim(p.nt_username))) else ''system'' end, [Database] = case when p.dbid = 0 then ''no database context'' else db_name(p.dbid) end, [Status] = p.status, [Open Transactions] = p.open_tran, [Command] = p.cmd, [Application] = p.program_name, [Wait Time] = p.waittime, [Wait Type] = case when p.waittype = 0 then ''not waiting'' else p.lastwaittype end, [Wait Resource] = case when p.waittype = 0 then '''' else p.waitresource end, [CPU] = p.cpu, [Physical IO] = p.physical_io, [Memory Usage] = p.memusage, [Login Time] = p.login_time, [Last Batch] = p.last_batch, [Host] = p.hostname, [Net Library] = p.net_library, [Net Address] = p.net_address, [Blocked By] = p.blocked, [Blocking] = 0, [Execution Context ID] = p.ecidinto ' + @proctab + ' from master.dbo.sysprocesses p with (NOLOCK) order by p.spid'exec (@stmt)-- =============================================-- create temporary indexes-- =============================================set @stmt = N'create index ' + @locktab + '_spid on ' + @locktab + '([Process ID])'exec (@stmt)set @stmt = N'create index ' + @locktab + '_object on ' + @locktab + '([Object])'exec (@stmt)set @stmt = N'create index ' + @proctab + '_spid on ' + @proctab + '([Process ID])'exec (@stmt)set @stmt = N'create index ' + @proctab + '_blockedby on ' + @proctab + '([Blocked By])'exec (@stmt)set transaction isolation level read committed-- =============================================-- replace placeholders get object names-- =============================================declare @lckdb sysnamedeclare @lckobjid integerdeclare @lckobj sysnamedeclare @lckindid smallintdeclare @lckind sysnameset @stmt = 'declare c1 cursor for select distinct [Database], [ObjID], [IdxID] from ' + @locktab + ' where [DBID] > 0 FOR READ ONLY'exec (@stmt)open c1fetch c1 into @lckdb, @lckobjid, @lckindidwhile @@fetch_status >= 0begin if (@lckobjid > 0) begin select @stmt ='update ' + @locktab + ' set [Table] = name, [ObjOwner] = user_name(uid) from ' + quotename(@lckdb, '[') + '.[dbo].[sysobjects] where id = ' + convert(nvarchar(10), @lckobjid) + ' and [Database] = ''' + @lckdb + ''' and [ObjID] = ' + convert(nvarchar(10), @lckobjid) exec (@stmt) select @stmt ='update ' + @locktab + ' set [Index] = name from ' + quotename(@lckdb, '[') + '.[dbo].[sysindexes] where id = ' + convert(nvarchar(10), @lckobjid) + ' and indid = ' + convert(nvarchar(10), @lckindid) + ' and [Database] = ''' + @lckdb + ''' and [IdxID] = ' + convert(nvarchar(10), @lckindid) exec (@stmt) end fetch c1 into @lckdb, @lckobjid, @lckindidenddeallocate c1set @stmt = 'update ' + @locktab + ' set [Object] = [Database] where [ObjID] = 0'exec (@stmt)set @stmt = 'update ' + @locktab + ' set [Object] = rtrim([Database]) + ''.'' + rtrim([ObjOwner]) + ''.'' + rtrim([Table]) where [ObjID] > 0'exec (@stmt)-- =============================================-- blocking-- =============================================set @stmt = 'update ' + @proctab + ' set [Blocking] = 1 where [Process ID] in (select [Blocked By] from ' + @proctab + ' where [Blocked By] > 0)'exec (@stmt)select [spid] = @idreturn(0)-- =============================================-- end sp_MSset_current_activity-- =============================================go/*****End of No.340,WITH Name: sp_MSset_current_activity********/ N'tempdb..##lockinfo'这个表的建立不知道在哪里。 tempdb..##lockinfo应该是不需要这样写的。因为##的表都应该在TEMPDB里 请教数据库问题 表分区与分区视图 循环读取数据库,并输出其中字段内容的sql语句如何书写? 字符超过8000时如何存储(sql2000) 一个表里有几行数据一模一样,要保留一行,删掉其余的,怎么办,求解 回娘家求助SQL了。。最近太忙没时间上论坛~~ SQL2000 里面的CASE WHEN ... THEN ... 语句报错,如何处理? 请教:pubs数据库里的这个查询语句怎么写 做过数据分析的高手请进!!!(字段个数不定的分组计算问题)在线等候与您交流! 如何对表字段进行排序? 请问如何将下面oracle的SQL语句转换成SQL server的SQL文 如何用SQL语句修改字段为标识列!
报告有关锁的信息。
下面的示例显示 SQL Server 中当前持有的所有锁的信息。USE master
EXEC sp_lock
是死锁进程所执行的那句SQL语法!要知道是哪个操作引起死锁,并做出判断来处理它!
楼上就差的更远了。
用 sp_MSget_current_activity 51,2
返回发生死锁进程列表,至于判断发生死锁自己写过程。。
M$是用sp_MSset_current_activity null 取得。
死锁的SPID在座的大概都能得到。
我要的是该SPID的SQL内容,在脚本里得到,而不是管理器里。
这个在MSSQL2000帮助里查不到。
请告诉具体参数以及意义
ps:我还没来得及研究 有需要的就看看
/*****Begin of No.340,WITH Name: sp_MSset_current_activity********/
-- =============================================
-- sp_MSset_current_activity
-- =============================================
create procedure dbo.sp_MSset_current_activity @id int OUTPUT
asset transaction isolation level read uncommitted
set quoted_identifier on
set nocount on
set lock_timeout 5000declare @stmt as nvarchar(4000)
-- =============================================
-- make tables SPID depended
-- =============================================
declare @locktab as sysname
declare @proctab as sysname
declare @locktb2 as sysname
declare @proctb2 as sysnameset @id = @@spid
set @locktab = N'##lockinfo' + rtrim(convert(nvarchar(5), @id))
set @proctab = N'##procinfo' + rtrim(convert(nvarchar(5), @id))
set @locktb2 = N'tempdb..##lockinfo' + rtrim(convert(nvarchar(5), @id))
set @proctb2 = N'tempdb..##procinfo' + rtrim(convert(nvarchar(5), @id))-- =============================================
-- delete temp tables
-- =============================================
if (object_id(@locktb2) is not null)
exec(N'drop table ' + @locktab)if (object_id(@proctb2) is not null)
exec(N'drop table ' + @proctab)-- =============================================
-- lockinfo table
-- =============================================
set @stmt =
N'select [Process ID] = l.req_spid,
[DBID] = l.rsc_dbid,
[Database] = db_name(l.rsc_dbid),
[ObjID] = l.rsc_objid,
[Object] = convert(nvarchar(386), ''''),
[Table] = convert(sysname, ''''),
[ObjOwner] = convert(sysname, ''''),
[IdxID] = l.rsc_indid,
[Index] = convert(sysname, ''''),
[Lock Type] = (select substring (v.name, 1, 4) from master.dbo.spt_values v where l.rsc_type = v.number and v.type = ''LR''),
[Mode] = (select substring (u.name, 1, 8) from master.dbo.spt_values u where l.req_mode + 1 = u.number and u.type = ''L''),
[Status] = (select substring (x.name, 1, 5) from master.dbo.spt_values x where l.req_status = x.number and x.type = ''LS''),
[Owner] = (select substring (o.name, 1, 8) from master.dbo.spt_values o where l.req_ownertype = o.number and o.type = ''LO''),
[Resource] = substring (rsc_text, 1, 16)
into ' + @locktab + ' from master.dbo.syslockinfo l with (NOLOCK) order by l.req_spid'
exec (@stmt)-- =============================================
-- processinfo table
-- =============================================
set @stmt =
N'select [Process ID] = p.spid,
[User] = case when p.spid > 6
then convert(sysname, ISNULL(suser_sname(p.sid), rtrim(p.nt_domain) + ''\'' + rtrim(p.nt_username)))
else ''system''
end,
[Database] = case when p.dbid = 0
then ''no database context''
else db_name(p.dbid)
end,
[Status] = p.status,
[Open Transactions] = p.open_tran,
[Command] = p.cmd,
[Application] = p.program_name,
[Wait Time] = p.waittime,
[Wait Type] = case when p.waittype = 0
then ''not waiting''
else p.lastwaittype
end,
[Wait Resource] = case when p.waittype = 0
then ''''
else p.waitresource
end,
[CPU] = p.cpu,
[Physical IO] = p.physical_io,
[Memory Usage] = p.memusage,
[Login Time] = p.login_time,
[Last Batch] = p.last_batch,
[Host] = p.hostname,
[Net Library] = p.net_library,
[Net Address] = p.net_address,
[Blocked By
] = p.blocked,
[Blocking] = 0,
[Execution Context ID] = p.ecid
into ' + @proctab + ' from master.dbo.sysprocesses p with (NOLOCK) order by p.spid'
exec (@stmt)-- =============================================
-- create temporary indexes
-- =============================================
set @stmt = N'create index ' + @locktab + '_spid on ' + @locktab + '([Process ID])'
exec (@stmt)set @stmt = N'create index ' + @locktab + '_object on ' + @locktab + '([Object])'
exec (@stmt)set @stmt = N'create index ' + @proctab + '_spid on ' + @proctab + '([Process ID])'
exec (@stmt)set @stmt = N'create index ' + @proctab + '_blockedby on ' + @proctab + '([Blocked By])'
exec (@stmt)set transaction isolation level read committed-- =============================================
-- replace placeholders get object names
-- =============================================
declare @lckdb sysname
declare @lckobjid integer
declare @lckobj sysname
declare @lckindid smallint
declare @lckind sysnameset @stmt = 'declare c1 cursor for select distinct [Database], [ObjID], [IdxID] from ' + @locktab + ' where [DBID] > 0 FOR READ ONLY'
exec (@stmt)open c1
fetch c1 into @lckdb, @lckobjid, @lckindidwhile @@fetch_status >= 0
begin
if (@lckobjid > 0)
begin
select @stmt ='update ' + @locktab + ' set [Table] = name, [ObjOwner] = user_name(uid) from ' + quotename(@lckdb, '[') + '.[dbo].[sysobjects] where id = ' + convert(nvarchar(10), @lckobjid) + ' and [Database] = ''' + @lckdb + ''' and [ObjID] = ' + convert(nvarchar(10), @lckobjid)
exec (@stmt)
select @stmt ='update ' + @locktab + ' set [Index] = name from ' + quotename(@lckdb, '[') + '.[dbo].[sysindexes] where id = ' + convert(nvarchar(10), @lckobjid) + ' and indid = ' + convert(nvarchar(10), @lckindid) + ' and [Database] = ''' + @lckdb + ''' and [IdxID] = ' + convert(nvarchar(10), @lckindid)
exec (@stmt)
end
fetch c1 into @lckdb, @lckobjid, @lckindid
end
deallocate c1set @stmt = 'update ' + @locktab + ' set [Object] = [Database] where [ObjID] = 0'
exec (@stmt)set @stmt = 'update ' + @locktab + ' set [Object] = rtrim([Database]) + ''.'' + rtrim([ObjOwner]) + ''.'' + rtrim([Table]) where [ObjID] > 0'
exec (@stmt)-- =============================================
-- blocking
-- =============================================
set @stmt = 'update ' + @proctab + ' set [Blocking] = 1 where [Process ID] in (select [Blocked By] from ' + @proctab + ' where [Blocked By] > 0)'
exec (@stmt)select [spid] = @idreturn(0)
-- =============================================
-- end sp_MSset_current_activity
-- =============================================
go
/*****End of No.340,WITH Name: sp_MSset_current_activity********/
这个表的建立不知道在哪里。
应该是不需要这样写的。因为##的表都应该在TEMPDB里