>dbcc traceon(3604)
>go
>dbcc sqltext(sp_id)
>go
能看到连接sp_id的运行的sql语句
sp__id可由sp_who查出，这样如果数据库出现lock的时候可以查询是什么sql原因造成的
写成存储过程更简单！我已经用很久了，呵呵 if exists (select 1 from sysobjects where (sysstat & 7) = 4 and name = 'sp_sql')
begin
drop procedure sp_sql
end
go
print "Installing sp_sql"
go
create procedure sp_sql
@spid smallint
as
declare
@retvalue int
begin
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
set flushmessage on
set nocount on
if (@spid > 0)
begin
dbcc traceon(3604)
print "==============================================================================="
dbcc sqltext(@spid)
print "==============================================================================="
dbcc traceoff(3604)
select @retvalue = @@error
end
else
begin
select @retvalue = -200
end
return @retvalue
end
go
exec sp_procxmode 'sp_sql', 'anymode'
go
grant execute on sp_sql to public
go
begin
drop procedure sp_sql
end
go
print "Installing sp_sql"
go
create procedure sp_sql
@spid smallint
as
declare
@retvalue int
begin
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
set flushmessage on
set nocount on
if (@spid > 0)
begin
dbcc traceon(3604)
print "==============================================================================="
dbcc sqltext(@spid)
print "==============================================================================="
dbcc traceoff(3604)
select @retvalue = @@error
end
else
begin
select @retvalue = -200
end
return @retvalue
end
go
exec sp_procxmode 'sp_sql', 'anymode'
go
grant execute on sp_sql to public
go