INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Microsoft SQL Server: Setup and Administration FAQ

General Information

Custom Version of sp_who2 called sp_who3 by mrdenny
Posted: 15 Feb 07 (Edited 15 Oct 07)

Below is an updated version of sp_who2 which I called sp_who3.  There are separate versions for SQL 7, SQL 2000 and SQL 2005.  There are actually two procedures created.  sp_blocked and sp_who3.  sp_blocked is used by sp_who3.

sp_who3 accepts three input parameters (while sp_who2 supports two parameters).  Like sp_who2, sp_who3 supports passing a specific spid and the word active.  It also supports the work blocked.  Passing the work blocked will return a list of all blocking spids and all blocked spids.  This will assist in tracing back a blocking tree to find the start of the blocking.  Passing the active parameter will return the same data as sp_who2 with the active parameter.

When you run sp_who3 with a spid as the parameter 6 or 7 second sets will be returned.  The first is the output from sp_who.  The second is the output from sp_who2.  The third is the input buffer (first 255 characters of the last command run).  The Fourth will not always appear.  When it does it will show you the FULL text of the T/SQL command that is being executed (it doesn't appear in SQL 2005 as Microsoft removed the function that returns this data, as soon as I find a work around I'll post an update).  The Fifth is the information about the spid in the sysprocesses table.  The sixth will be the information from the sysprocess table about all spids being blocked by this spid (if no processes are being blocked a message to this extent will be displayed).  The seventh record set will be the lock information for the requested process.For SQL 2005 where is says sysprocesses it is actually pulling from sys.sysprocesses.

Each version grants execute rights to all users via the public role in the master database (last couple of lines of the script).  If you do not want users to be able to execute this command remove the GRANT statement.

In SQL 2005 if users have the right to run sp_who3 because of default SQL Server 2005 security they will only be able to see information about their current process.  In order for them to be able to view information about all the processes grant their login the VIEW SERVER STATE right using this command.

CODE --> SQL 2005

GRANT VIEW SERVER STATE TO [DOMAIN] AS [sa]
GO


Below is the actual code for the sp_who3 procedures.

CODE --> SQL 7

use master
if exists(select * from master.dbo.sysobjects where name = 'sp_blocked')
    drop procedure sp_blocked
go
create procedure sp_blocked
    @spid int
as
create table #Blocked
(spid int)
insert into #Blocked
(spid)
values
(@spid)

while @@ROWCOUNT <> 0
BEGIN
    insert into #Blocked
    (spid)
    select spid
        from master.dbo.sysprocesses
        where blocked in (select spid from #Blocked)
        and spid not in (select spid from #Blocked)
END

delete from #Blocked
where spid = @spid

if exists(select * from #Blocked)
BEGIN
    select *
    from master.dbo.sysprocesses
    where spid in (select spid from #Blocked)
END
else
BEGIN
    select 'No Processes are being blocked by spid ' + convert(varchar(20), @spid) + '.' as 'System Message'
END

drop table #Blocked
go
print 'sp_blocked created.'



USE master
print 'Creating sp_who3'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_who3]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_who3
    @spid sysname = null
as
SET NOCOUNT ON
if @spid is null
BEGIN
    exec sp_who2
END
ELSE
BEGIN
    if lower(convert(varchar(10), @spid)) = 'active'
    BEGIN
        exec sp_who2 active
    END
    ELSE
    BEGIN
        declare @spid_i int
        set @spid_i = @spid
        exec sp_who @spid_i
        exec sp_who2 @spid_i
        dbcc inputbuffer (@spid_i)
        select * from master.dbo.sysprocesses where spid = @spid_i
        exec sp_blocked @spid_i
        exec sp_lock @spid_i
    END
END
GO
grant exec on sp_who3 to public
go

CODE --> SQL 2000

use master
if exists(select * from master.dbo.sysobjects where name = 'sp_blocked')
    drop procedure sp_blocked
go
create procedure dbo.sp_blocked
    @spid int
as
create table #Blocked
(spid int)
insert into #Blocked
(spid)
values
(@spid)

while @@ROWCOUNT <> 0
BEGIN
    insert into #Blocked
    (spid)
    select spid
        from master.dbo.sysprocesses
        where blocked in (select spid from #Blocked)
        and spid not in (select spid from #Blocked)
END

delete from #Blocked
where spid = @spid

delete from #Blocked
where spid is null

if exists(select * from #Blocked)
BEGIN
    select *
    from master.dbo.sysprocesses
    where spid in (select spid from #blocked)
END
else
BEGIN
    select 'No Processes are being blocked by spid ' + convert(varchar(20), @spid) + '.' as 'System Message'
END

drop table #Blocked
go
print 'sp_blocked created.'

USE master
print 'Creating sp_who3'

if exists (select * from dbo.sysobjects where name = 'sp_who3')
drop procedure [dbo].[sp_who3]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_who3
    @spid sysname = null
as
DECLARE @spid_i INT
DECLARE @spid_only bit
SET NOCOUNT ON
if @spid is null
BEGIN
    exec sp_who2
END
ELSE
BEGIN
    set @spid_only = 1
    if lower(cast(@spid as varchar(10))) = 'active'
    BEGIN
        set @spid_only = 0
        exec sp_who2 'active'
    END
    if lower(cast(@spid as varchar(10))) = 'blocked'
    BEGIN
        DECLARE @blocked TABLE
        (spid int,
        blocked int)

        INSERT INTO @blocked
        select spid, blocked
        from sysprocesses
        where blocked <> 0

        insert into @blocked
        select spid, blocked
        from sysprocesses
        where spid in (select blocked from @blocked)

        set @spid_only = 0
        select sysprocesses.spid as 'SPID',
            sysprocesses.status,
            sysprocesses.loginame as 'Login',
            sysprocesses.hostname as 'HostName',
            sysprocesses.blocked as 'BlkBy',
            sysdatabases.name as 'DBName',
            sysprocesses.cmd as 'Command',
            sysprocesses.cpu as 'CPUTime',
            sysprocesses.physical_io as 'DiskIO',
            sysprocesses.last_batch as 'LastBatch',
            sysprocesses.program_name as 'ProgramName',
            sysprocesses.spid as 'SPID'
        from sysprocesses
        left outer join sysdatabases on sysprocesses.dbid = sysdatabases.dbid
        where spid in (select spid from @blocked)
    END
    
    if @spid_only = 1
    BEGIN
        set @spid_i = @spid
        exec sp_who @spid_i
        exec sp_who2 @spid_i
        dbcc inputbuffer (@spid_i)
        /*Start Get Output Buffer*/
        if exists (select * from sysobjects where name = 'fn_get_sql')
        BEGIN
            declare @SQL_Handle binary(20)
            select @SQL_Handle = sql_handle
            from master.dbo.sysprocesses
            where spid = @spid_i
            
            if @SQL_Handle is not null
            BEGIN    
                Create Table #SQL
                (SQLText text)
                insert into #SQL
                 SELECT [text]
                    FROM ::fn_get_sql(@SQL_Handle)
                if exists(select * from #SQL)
                    select SQLText 'Full SQL Query' from #SQL
                drop table #SQL
            END
        END
        /*End Get Output Buffer*/
        select * from master.dbo.sysprocesses where spid = @spid_i
        exec sp_blocked @spid_i
        exec sp_lock @spid_i
    END
END
GO
print 'sp_who3 created.'
go
GRANT exec on sp_who3 to public
GO

CODE --> SQL 2005

use master
if exists(select * from master.sys.objects where name = 'sp_blocked')
drop procedure sp_blocked
go
create procedure dbo.sp_blocked
@spid int
as
create table #Blocked
(spid int)
insert into #Blocked
(spid)
values
(@spid)

while @@ROWCOUNT <> 0
BEGIN
insert into #Blocked
(spid)
select spid
from master.sys.sysprocesses
where blocked in (select spid from #Blocked)
and spid not in (select spid from #Blocked)
END

delete from #Blocked
where spid = @spid

delete from #Blocked
where spid is null

if exists(select * from #Blocked)
BEGIN
select *
from master.sys.sysprocesses
where spid in (select spid from #blocked)
END
else
BEGIN
select 'No Processes are being blocked by spid ' + convert(varchar(20), @spid) + '.' as 'System Message'
END

drop table #Blocked
go
print 'sp_blocked created.'

USE master
print 'Creating sp_who3'

if exists (select * from sys.objects where name = 'sp_who3')
drop procedure [dbo].[sp_who3]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_who3
@spid sysname = null
as

/*
Date Creator Action
2007.02.15 mrdenny Birth
2007.05.18 mrdenny Correct Full Query Text
2007.10.08 mrdenny Added Waiting Statement to Full Query RecordSet
*/

DECLARE @spid_i INT
DECLARE @spid_only bit
SET NOCOUNT ON
if @spid is null
BEGIN
exec sp_who2
END
ELSE
BEGIN
set @spid_only = 1
if lower(cast(@spid as varchar(10))) = 'active'
BEGIN
set @spid_only = 0
exec sp_who2 'active'
END
if lower(cast(@spid as varchar(10))) = 'blocked' or (isnumeric(@spid) = 1 and @spid < 0)
BEGIN
DECLARE @blocked TABLE
(spid int,
blocked int)

INSERT INTO @blocked
select spid, blocked
from sys.sysprocesses
where blocked <> 0

insert into @blocked
select spid, blocked
from sys.sysprocesses
where spid in (select blocked from @blocked)

set @spid_only = 0
select sys.sysprocesses.spid as 'SPID',
sys.sysprocesses.status,
sys.sysprocesses.loginame as 'Login',
sys.sysprocesses.hostname as 'HostName',
sys.sysprocesses.blocked as 'BlkBy',
sys.databases.name as 'DBName',
sys.sysprocesses.cmd as 'Command',
sys.sysprocesses.cpu as 'CPUTime',
sys.sysprocesses.physical_io as 'DiskIO',
sys.sysprocesses.last_batch as 'LastBatch',
sys.sysprocesses.program_name as 'ProgramName',
sys.sysprocesses.spid as 'SPID'
from sys.sysprocesses
left outer join sys.databases on sys.sysprocesses.dbid = sys.databases.database_id
where spid in (select spid from @blocked)
END

if @spid_only = 1
BEGIN
DECLARE @sql_handle varbinary(64)
DECLARE @stmt_start int
DECLARE @stmt_end int

set @spid_i = @spid

SELECT @sql_handle = sql_handle,
    @stmt_start = stmt_start,
    @stmt_end = stmt_end
from sys.sysprocesses
where spid = @spid_i

exec sp_who @spid_i
exec sp_who2 @spid_i
dbcc inputbuffer (@spid_i)
/*Start Get Output Buffer*/
select text as 'Full Query',
    case when @stmt_start < 0 then
        substring(text, @stmt_start/2, (@stmt_end/2)-(@stmt_start/2))
    else
        null
    end as 'Current Command'
from sys.dm_exec_sql_text(@sql_handle)
/*End Get Output Buffer*/
select * from master.sys.sysprocesses where spid = @spid_i
exec sp_blocked @spid_i
exec sp_lock @spid_i
END
END

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close