Post reply

dbcc inputbuffer question

  • March 7, 2008 at 10:43 am

    #786812

    I owe this to a person or persons unknown but it worked for me.

    Run sp_who or sp_who2 to obtain the process id (spid) then

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = (insert value of spid from sp_who2)

    SELECT * FROM ::fn_get_sql(@Handle)

    Last time I used this script it returned a max of 4000 characters, so it does have its limitations.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 13, 2008 at 11:19 pm

    #789455

    try this one ::fn_get_sql(Sql_handle)

  • March 14, 2008 at 3:22 am

    #789496

    For his function fn_getsql(),you need to apply SP3 or SP4 on your SQl server.I tried with this long back.It worked for me.

  • March 15, 2008 at 5:42 pm

    #790074

    Thats a nice looking page of the

    http://www.grumpyolddba.co.uk/monitoring/images/Procedure%20Cache%20Status.mhtml

    proceduce cache.

    Do you have any more information for this, i.e what do you check

    SchemaMgr Store (? IS)

    SystemRowsetStore

    Looks like it be worth investigating.

    The aged report (do you have the rdl for this)...:D

  • March 15, 2008 at 5:53 pm

    #790076

    bitbucket (3/7/2008)

    I owe this to a person or persons unknown but it worked for me.

    Run sp_who or sp_who2 to obtain the process id (spid) then

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = (insert value of spid from sp_who2)

    SELECT * FROM ::fn_get_sql(@Handle)

    Last time I used this script it returned a max of 4000 characters, so it does have its limitations.

    Running 2k sp3a... and I get only column headers back... Text column has nothing in it no matter which SPID I try... what's the trick here? Does the code need to be running when you run the function or ???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 15, 2008 at 6:06 pm

    #790078

    i just tired it seemed fine to me

    Try anything above 50 and see if this works.

    Maybe not working for < 50 as this is system processes.

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 51

    SELECT * FROM ::fn_get_sql(@Handle)

  • March 15, 2008 at 6:19 pm

    #790080

    Thanks for the feedback, Tracey. Except for the SPID (I had an active connection with 58), that's the exact code I used. Got zero rows in return no matter which active spid I used.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 15, 2008 at 7:12 pm

    #790087

    Hmm then not so sure why nothing comes out.

    Try this one

    ------------------------------------------------------

    --Get just one details from the spid (A)

    ------------------------------------------------------

    SELECT r.session_id, r.status, r.start_time, r.command

    FROM sys.dm_exec_requests r

    ------------------------------------------------------

    --Get SPID then pass back FROM (A)

    ------------------------------------------------------

    SELECT r.session_id, r.status, r.start_time, r.command, s.text

    FROM sys.dm_exec_requests r

    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

    WHERE r.session_id = '58'

  • March 15, 2008 at 7:16 pm

    #790089

    This doesn't work on Sql2000 does it?

    I seem to recall that the "hooks" were in Sql2000, but they never returned anything. Maybe there is some patch or tool that you need to apply for 2000.

    It works fine in Sql2005.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 15, 2008 at 7:32 pm

    #790093

    Oh is this for SQL 2000 sorry thought it was for SQL 2005.

    I could not get the handles to work in SQL 2000

    Try this for SQL 2000 (This is what i used a lot )

    TO run procedure exec sp_now

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE sp_Now

    as

    set nocount on

    declare @handle binary(20),

    @spid smallint,

    @rowcnt smallint,

    @output varchar(500)

    declare ActiveSpids CURSOR FOR

    select sql_handle, spid

    from sysprocesses

    where sql_handle <> 0x0000000000000000000000000000000000000000

    --and spid <> @@SPID

    order by cpu desc

    OPEN ActiveSpids

    FETCH NEXT FROM ActiveSpids

    INTO @handle,

    @spid

    set @rowcnt = @@CURSOR_ROWS

    print '===================='

    print '= CURRENT ACTIVITY ='

    print '===================='

    print ' '

    set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)

    print @output

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    print ' '

    print ' '

    print 'O' + replicate('x',120) + 'O'

    print 'O' + replicate('x',120) + 'O'

    print ' '

    print ' '

    print ' '

    select 'loginame' = left(loginame, 30),

    'hostname' = left(hostname,30),

    'datagbase' = left(db_name(dbid),30),

    'spid' = str(spid,4,0),

    'block' = str(blocked,5,0),

    'phys_io' = str(physical_io,8,0),

    'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END ,

    'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),

    'program_name' = left(program_name,50),

    'command' = cmd,

    'lastwaittype' = left(lastwaittype,15),

    'login_time' = convert(char(19),login_time,120),

    'last_batch' = convert(char(19),last_batch,120),

    'status' = left(status, 10),

    'nt_username' = left(nt_username,20)

    from master..sysprocesses

    where spid = @spid

    print ' '

    print ' '

    -- Dump the inputbuffer to get an idea of what the spid is doing

    dbcc inputbuffer(@spid)

    print ' '

    print ' '

    -- Use the built-in function to show the exact SQL that the spid is running

    select * from ::fn_get_sql(@handle)

    FETCH NEXT FROM ActiveSpids

    INTO @handle,

    @spid

    END

    close ActiveSpids

    deallocate ActiveSpids

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • March 16, 2008 at 9:31 am

    #790144

    My bad, Tracey... I said in my post that I was "Running 2k sp3a"... not a real obvious note on my part... sorry and thanks for the idea.

    Barry, thanks for the note about the hooks being there and the functionality not.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 17, 2008 at 11:56 am

    #790477

    TRACEY,

    Thanks copied your posting and it works in 2000 and Express 2005.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 17, 2008 at 1:45 pm

    #790542

    Hi adam yes that view is great i use it all the time.

    You got one for SQL 2005 .........sp_now 😀

  • July 23, 2008 at 2:30 pm

    #847380

    If you don't get records, it's probably because of a parallel query. Change the where clause to look at the ecid as well.

    where spid=(yourspid) and ecid=0

  • November 14, 2008 at 10:14 am

    #898727

    Adam, good job! really good view!!!

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply