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
    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
        LockDateLocal = CASE Locked
                           WHEN 0 THEN @nowLocal
                           ELSE LockDateLocal
            @lockAge = CASE Locked
                WHEN 0 THEN 0
                ELSE DATEDIFF(second, LockDate, @now)
            @lockCookie = LockCookie = CASE Locked
                WHEN 0 THEN LockCookie + 1
                ELSE LockCookie
            @itemShort = CASE Locked
                WHEN 0 THEN SessionItemShort
                ELSE NULL
            @textptr = CASE Locked
                WHEN 0 THEN TEXTPTR(SessionItemLong)
                ELSE NULL
            @length = CASE Locked
                WHEN 0 THEN DATALENGTH(SessionItemLong)
                ELSE NULL
            @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
            @actionFlags = CASE
                WHEN (Flags & 1) <> 0 THEN 1
                ELSE 0
        WHERE SessionId = @id
        IF @length IS NOT NULL BEGIN
            READTEXT dbo.ASPStateTempSessions.SessionItemLong @textptr 0 @length

        RETURN 0

1 Answer 1


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.

  • @olle switch param get_full_inner_text to 0 and you'll see which statement is the blocker. In your case I suppose it's the UPDATE statement. See what you can do to make it faster (indexes..indexes .. or maybe indexes :-) ).
    – Marian
    Commented Nov 2, 2011 at 15:07
  • @Marian but that doesn't explain why it's executing CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3] ever x minutes (not like clockwork). And that is what looks like the blocker.
    – olle
    Commented Nov 2, 2011 at 15:13
  • 5
    @olle: it's not executing CREATE PROCEDURE TempGetStateItemExclusive3. It's executing only the procedure itself - TempGetStateItemExclusive3 with it's parameters. But the output of the SP_WhoIsActive show the complete text of the procedure TempGetStateItemExclusive3 for you to be able to have a bigger picture. Better check also with SQL Profiler to see all sessions. There you'll see exactly the executions.
    – Marian
    Commented Nov 2, 2011 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.