dbcc inputbuffer question

  • I am trying to find out the sql statement run by a process. To do this I am using the DBCC INPUTBUFFER (process #) command. However, the eventinfo column of the output, cuts off the sql statement. Does anyone know how I can see the full statement?

  • In eventinfo column for DBCC inputbuffer you can see the first 256 characters only. Why not run a SQL profiler. Textdata column will display the complete code in it.

    SQL DBA.

  • Thank you for your information about profiler. I will try that. But I was also curious to know why BOL says eventinfo is nvchar(4000) and it will only show 256 characters.

    From BOL: EventInfo nvarchar(4000)

    For an EventType of RPC, EventInfo contains only the procedure name. For an EventType of Language, only the first 4000 characters of the event are displayed.

  • I checked both 2000 and 2005 and it shows nvarchar(255) -

    For an EventType of RPC, EventInfo contains only the procedure name. For an EventType of Language or No Event, only the first 255 characters of the event are displayed.

    SQL DBA.

  • there is a DMV exposing this in sql2005, I'll have to do a little search on it.

    this comes from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2892190&SiteID=1

    (I don't have my own scripts available for the moment)

    -- CREATE VIEW who_vw

    -- AS

    SELECT

    p.spid AS [SPID]

    ,DB_NAME(p.dbid) AS [DBName]

    ,COALESCE(OBJECT_NAME(txt.objectID,txt.DBID),

    CASE

    WHEN txt.encrypted = 1

    THEN 'Encrypted'

    WHEN r.session_id IS NULL

    THEN txt.text

    ELSE LTRIM(SUBSTRING(txt.text, r.statement_start_offset / 2 + 1

    ,((CASE WHEN r.statement_end_offset = -1

    THEN DATALENGTH(txt.text)

    ELSE r.statement_end_offset

    END) - r.statement_start_offset) / 2))

    END) AS [Query]

    ,p.loginame AS [Login]

    ,p.hostname AS [Host Name]

    ,p.status AS [Status]

    ,p.blocked AS [BlkBy]

    ,ISNULL(t.trancount,0) AS [TranCount]

    ,ISNULL(l.lockcount,0) AS [LockCount]

    ,l.resource_type AS [LockType]

    ,l.request_mode AS [LockMode]

    ,l.request_status AS [LockStatus]

    ,r.wait_type AS [WaitType]

    ,r.percent_complete AS [PercentComplete]

    ,r.estimated_completion_time AS [EstCompTime]

    ,p.cpu AS [CPU]

    ,p.physical_io AS [IO]

    ,c.num_reads AS [Reads]

    ,c.num_writes AS [Writes]

    ,c.last_read AS [LastRead]

    ,c.last_write AS [LastWrite]

    ,p.login_time AS [StartTime]

    ,p.last_batch AS [LastBatch]

    ,p.PROGRAM_NAME AS [Program Name]

    ,p.spid AS [SPID2]

    FROM sys.sysprocesses p

    INNER JOIN sys.dm_exec_connections c (NOLOCK)

    ON c.session_id = p.spid

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt

    LEFT JOIN sys.dm_exec_requests r

    ON c.session_id = r.session_id

    LEFT OUTER JOIN

    (

    SELECT

    session_id

    ,database_id = MAX(database_id)

    ,trancount = COUNT(*)

    FROM

    sys.dm_tran_session_transactions t

    INNER JOIN sys.dm_tran_database_transactions dt

    ON t.transaction_id = dt.transaction_id

    GROUP BY session_id

    ) t

    ON t.session_id = p.spid

    LEFT OUTER JOIN

    (

    SELECT

    request_session_id

    ,database_id = MAX(resource_database_id)

    ,resource_type

    ,request_status

    ,request_mode

    ,lockcount = COUNT(*)

    FROM

    sys.dm_tran_locks (NOLOCK)

    GROUP BY request_session_id, resource_type, request_mode, request_status

    ) l

    ON p.spid = l.request_session_id

    WHERE p.spid <> @@SPID

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (3/4/2008)


    there is a DMV exposing this in sql2005, I'll have to do a little search on it.

    this comes from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2892190&SiteID=1

    (I don't have my own scripts available for the moment)

    -- CREATE VIEW who_vw

    -- AS

    SELECT

    p.spid AS [SPID]

    ,DB_NAME(p.dbid) AS [DBName]

    ,COALESCE(OBJECT_NAME(txt.objectID,txt.DBID),

    CASE

    WHEN txt.encrypted = 1

    THEN 'Encrypted'

    WHEN r.session_id IS NULL

    THEN txt.text

    ELSE LTRIM(SUBSTRING(txt.text, r.statement_start_offset / 2 + 1

    ,((CASE WHEN r.statement_end_offset = -1

    THEN DATALENGTH(txt.text)

    ELSE r.statement_end_offset

    END) - r.statement_start_offset) / 2))

    END) AS [Query]

    ,p.loginame AS [Login]

    ,p.hostname AS [Host Name]

    ,p.status AS [Status]

    ,p.blocked AS [BlkBy]

    ,ISNULL(t.trancount,0) AS [TranCount]

    ,ISNULL(l.lockcount,0) AS [LockCount]

    ,l.resource_type AS [LockType]

    ,l.request_mode AS [LockMode]

    ,l.request_status AS [LockStatus]

    ,r.wait_type AS [WaitType]

    ,r.percent_complete AS [PercentComplete]

    ,r.estimated_completion_time AS [EstCompTime]

    ,p.cpu AS [CPU]

    ,p.physical_io AS [IO]

    ,c.num_reads AS [Reads]

    ,c.num_writes AS [Writes]

    ,c.last_read AS [LastRead]

    ,c.last_write AS [LastWrite]

    ,p.login_time AS [StartTime]

    ,p.last_batch AS [LastBatch]

    ,p.PROGRAM_NAME AS [Program Name]

    ,p.spid AS [SPID2]

    FROM sys.sysprocesses p

    INNER JOIN sys.dm_exec_connections c (NOLOCK)

    ON c.session_id = p.spid

    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt

    LEFT JOIN sys.dm_exec_requests r

    ON c.session_id = r.session_id

    LEFT OUTER JOIN

    (

    SELECT

    session_id

    ,database_id = MAX(database_id)

    ,trancount = COUNT(*)

    FROM

    sys.dm_tran_session_transactions t

    INNER JOIN sys.dm_tran_database_transactions dt

    ON t.transaction_id = dt.transaction_id

    GROUP BY session_id

    ) t

    ON t.session_id = p.spid

    LEFT OUTER JOIN

    (

    SELECT

    request_session_id

    ,database_id = MAX(resource_database_id)

    ,resource_type

    ,request_status

    ,request_mode

    ,lockcount = COUNT(*)

    FROM

    sys.dm_tran_locks (NOLOCK)

    GROUP BY request_session_id, resource_type, request_mode, request_status

    ) l

    ON p.spid = l.request_session_id

    WHERE p.spid <> @@SPID

    haha! That's my view!!! I feel special ...

    http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx

  • Thanks for all of your responses. I will try Adam's view and see if it gives me the results I need.

  • I posted a custom rdl which does this - the query is a bit more simple. I don't usually create views or procs for this type of work as I may wish to add filters so generally a query works better.

    http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm

    you can extract the query from the rdl file

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • You can filter a view ...

    Personally, I prefer procs and views as you can create a wrapper process to keep them in a "admin" or "dba" database on all your servers so that you have a repository of all your scripts available no matter what server you're on.

    To each their own.

  • Adam Bean (3/4/2008)


    haha! That's my view!!! I feel special ...

    http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx

    Dang, Adam... why don't you put your name on that bad boy! 😉

    --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)

  • Adam Bean (3/4/2008)haha! That's my view!!! ...

    This prooves once again the world is round :w00t:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I do that in my procs, but never with views ... I just updated it to include the view.

    It still needs some work though ...

  • You get the sql by double-clicking on the process, which I think solves the original problem - although I'm not sure if there would be truncation.

    the alternatives posted seem interesting though.

  • Hi

    Why not use activity monitor?

    Gives the last SQL batch for the process...

  • ian treasure (3/6/2008)


    Hi

    Why not use activity monitor?

    Gives the last SQL batch for the process...

    You do not get the query text, transaction count, lock count, etc. and you can only sort by columns. The view allows you to do whatever you want to do (sorting, custom filtering, joining back to other dmv's, etc.).

    Again, to each their own, everyone has their ways of accomplishing similar tasks.

Viewing 15 posts - 1 through 15 (of 31 total)

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