Sql-server – sp_whoisactive keeps returning create procedure statements

sql server

When I run sp_whoisactive on my server it often shows create statements being run for stored procedures but this doesn't make any sense since the procedure already exists.

See the following example:

dd hh:mm:ss.mss session_id  sql_text
00 00:00:00.120 103 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.140 274 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
00 00:00:00.133 324 CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]

This is from one run of sp_whoisactive it looks like there are three sessions creating the procedure. Is this something I am misinterpreting or if not how can I find out where this is coming from? These create statements are blocking actual inserts.

I am executing 

EXEC sp_WhoIsActive 
     @find_block_leaders = 1, 
     @sort_order = '[blocked_session_count] DESC',
     @get_full_inner_text = 1

The batch being executed is (which is a stored procedure from asp.net SQL Server based session state but I am seeing the exact same thing for other stored procedures as well)

CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3]
        @id         tSessionId,
        @itemShort  tSessionItemShort OUTPUT,
        @locked     bit OUTPUT,
        @lockAge    int OUTPUT,
        @lockCookie int OUTPUT,
        @actionFlags int OUTPUT
AS
    DECLARE @textptr AS tTextPtr
    DECLARE @length AS int
    DECLARE @now AS datetime
    DECLARE @nowLocal AS datetime

    SET @now = GETUTCDATE()
    SET @nowLocal = GETDATE()

    UPDATE dbo.ASPStateTempSessions
    SET Expires = DATEADD(n, Timeout, @now), 
        LockDate = CASE Locked
                      WHEN 0 THEN @now
                      ELSE LockDate
                   END,
        LockDateLocal = CASE Locked
                           WHEN 0 THEN @nowLocal
                           ELSE LockDateLocal
                        END,
            @lockAge = CASE Locked
                WHEN 0 THEN 0
                ELSE DATEDIFF(second, LockDate, @now)
                END,
            @lockCookie = LockCookie = CASE Locked
                WHEN 0 THEN LockCookie + 1
                ELSE LockCookie
                END,
            @itemShort = CASE Locked
                WHEN 0 THEN SessionItemShort
                ELSE NULL
                END,
            @textptr = CASE Locked
                WHEN 0 THEN TEXTPTR(SessionItemLong)
                ELSE NULL
                END,
            @length = CASE Locked
                WHEN 0 THEN DATALENGTH(SessionItemLong)
                ELSE NULL
                END,
            @locked = Locked,
            Locked = 1,

            /* If the Uninitialized flag (0x1) if it is set,
               remove it and return InitializeItem (0x1) in actionFlags */
            Flags = CASE
                WHEN (Flags & 1) <> 0 THEN (Flags & ~1)
                ELSE Flags
                END,
            @actionFlags = CASE
                WHEN (Flags & 1) <> 0 THEN 1
                ELSE 0
                END
        WHERE SessionId = @id
        IF @length IS NOT NULL BEGIN
            READTEXT dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length
        END

        RETURN 0

Best Answer

Please show us the parameters of the execution of SP_whoIsActive. I remember there was a parameter switched between showing the actual statement or the batch/procedure that contained that statement.

If I'm not wrong:

@get_full_inner_text = 0 -- 1-shows SP/batch, 0-shows statement;

should be the one. Switch it to 0 and see if it's changing the way the output is shown.

Related Question