以sa登陆
>dbcc traceon(3604)
>go
>dbcc sqltext(sp_id)
>go
能看到连接sp_id的运行的sql语句
sp__id可由sp_who查出,这样如果数据库出现lock的时候可以查询是什么sql原因造成的
写成存储过程更简单!我已经用很久了,呵呵
>dbcc traceon(3604)
>go
>dbcc sqltext(sp_id)
>go
能看到连接sp_id的运行的sql语句
sp__id可由sp_who查出,这样如果数据库出现lock的时候可以查询是什么sql原因造成的
![](http://blog.itpub.net//imgs/comment_icon1.gif)
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