[?] dbcc inputbuffer

  • Does anyone know of a way to either get to the underlying code being run by dbcc inputbuffer or know of another way to pull the same information....if it is even possible? I would like to be able to see more than the first 255 characters.

    Thanks in advance!

  • I'm not sure how to change the dbcc inputbuffer, I use SQL Profiler and monitor the SQL:BatchStarting or the SQL:StmtStarting TSQL Events making sure to include the textdata column. This gives you the complete statements your users are sending. I save mine in a table and then I can work with the queries later.

    HTH,

    Michelle



    Michelle

  • I am pretty sure it is not possible. Trace I don't believe even uses inputbuffer if you read all the sections on it in SQL BOL. In SQL 2000 you can set up a TRACE like profiler does and view the output but it goes to a buffer for receiving and is slow going in QA.

    However, EM does use it when looking at current activity and it also only shows 255 characters. I would think it safe to assume they would've done it there if could be done.

    Edited by - antares686 on 01/23/2003 2:34:20 PM

  • In SP 3 there is a new function fn_get_sql, that can read more than 255 characters.

    Here an example from the sp3readme.htm

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52

    SELECT * FROM ::fn_get_sql(@Handle)

    I could not see anything, but I did not try very hard.

    Joachim.

  • Profilor is a very costly operation, I am using a different method to get which batch of the code is running.....

    I actuallty take the output from sp_lock and convert this output with this procedure for running spid's.....

    This procedure returns where what are the objects and used by procedure at any moment. now check ur code to see where you are using the code which is having a combination of all these tables.

    I know this is a bit tricky way to find out where are you in the procedure, but that is the only workable method I know as is profilor is costly and if a statement is running till the next statement fires you will not know where you r in the procedure....

    If interested compile this procedure it puts data into PH_LOCK_TMP1. Fire a query on this table where spid = ? and you will see all the tables used by that spid....

    IF OBJECT_ID ('SP_PH_LOCK') IS NOT NULL DROP PROC SP_PH_LOCK

    GO

    -- EXEC SP_PH_LOCK @FLG=1

    -- EXEC SP_PH_LOCK 70 -- RUN FOR SPID (DEFAULT)

    -- EXEC SP_PH_LOCK @TABLENAME='ACAP303M0',@DBNAME = 'DWHLIVE', @status = 'GRANT', @SPID=70

    CREATE PROC SP_PH_LOCK

    @SPID INTEGER=NULL,

    @TABLENAME VARCHAR(200)='%',

    @DBNAME VARCHAR(200)='%',

    @status VARCHAR(200)='%',

    @FLG BIT = '0'

    AS

    SET NOCOUNT ON

    DECLARE @SQL_STMT NVARCHAR(4000)

    IF OBJECT_ID ('PH_LOCK_TMP1') IS NOT NULL DROP TABLE PH_LOCK_TMP1

    SELECT CONVERT (SMALLINT, REQ_SPID) AS SPID,

    DB_NAME(RSC_DBID) AS DBNAME,

    convert(varchar(200),'') AS OBJNAME,

    convert(varchar(200),'') AS INDNAME,

    SUBSTRING (V.NAME, 1, 4) AS TYPE,

    SUBSTRING (RSC_TEXT, 1, 16) AS RESOURCE,

    SUBSTRING (U.NAME, 1, 8) AS MODE,

    SUBSTRING (X.NAME, 1, 5) AS STATUS,

    RSC_OBJID AS OBJID,

    RSC_INDID AS INDID,

    RSC_DBID AS DBID,

    GETDATE() AS RUNTIME

    INTO PH_LOCK_TMP1

    FROM MASTER.DBO.SYSLOCKINFO A WITH (NOLOCK),

    MASTER.DBO.SPT_VALUES V WITH (NOLOCK),

    MASTER.DBO.SPT_VALUES X WITH (NOLOCK),

    MASTER.DBO.SPT_VALUES U WITH (NOLOCK)

    WHERE A.RSC_TYPE = V.NUMBER

    AND V.TYPE = 'LR'

    AND A.REQ_STATUS = X.NUMBER

    AND X.TYPE = 'LS'

    AND A.REQ_MODE + 1 = U.NUMBER

    AND U.TYPE = 'L'

    ORDER BY SPID

    DELETE PH_LOCK_TMP1 WHERE SPID = @@SPID

    DECLARE @DBID VARCHAR(200)

    DECLARE C2 CURSOR FOR

    SELECT DISTINCT DBID FROM PH_LOCK_TMP1 WHERE OBJID <>0

    OPEN C2

    WHILE 0=0

    BEGIN

    FETCH C2 INTO

    @DBID

    IF @@FETCH_STATUS <> 0

    BREAK

    SET @SQL_STMT = 'USE ' + DB_NAME(@DBID) + ' UPDATE ' + DB_NAME() + '..PH_LOCK_TMP1 SET OBJNAME = (select name from sysobjects with (nolock) where id = OBJID) WHERE OBJID <>0 AND DBID=' + @DBID

    --PRINT @SQL_STMT

    EXEC SP_EXECUTESQL @SQL_STMT

    SET @SQL_STMT = 'USE ' + DB_NAME(@DBID) + ' UPDATE ' + DB_NAME() + '..PH_LOCK_TMP1 SET INDNAME = A.NAME FROM SYSINDEXES A WITH (NOLOCK), ' + DB_NAME() + '..PH_LOCK_TMP1 B WHERE A.ID = B.OBJID AND A.INDID=B.INDID AND B.INDID <>0'

    PRINT @SQL_STMT

    EXEC SP_EXECUTESQL @SQL_STMT

    END

    CLOSE C2

    DEALLOCATE C2

    IF @FLG = 0

    BEGIN

    IF @SPID IS NULL

    BEGIN

    SELECT *

    FROM PH_LOCK_TMP1

    WHERE OBJID <>0 AND SPID <> @@SPID

    AND OBJNAME LIKE '%' + @TABLENAME + '%'

    AND DBNAME LIKE '%' + @DBNAME + '%'

    AND STATUS LIKE '%' + @status + '%'

    --AND SPID = @SPID

    END

    ELSE

    BEGIN

    SELECT *

    FROM PH_LOCK_TMP1

    WHERE OBJID <>0 AND SPID <> @@SPID

    AND OBJNAME LIKE '%' + @TABLENAME + '%'

    AND DBNAME LIKE '%' + @DBNAME + '%'

    AND STATUS LIKE '%' + @status + '%'

    AND SPID = @SPID

    END

    IF OBJECT_ID ('PH_LOCK_TMP1') IS NOT NULL DROP TABLE PH_LOCK_TMP1

    END

    GO

    exec sp_ph_lock @flg=1

    go

    select distinct SPID,DBNAME,OBJNAME,INDNAME from PH_LOCK_TMP1

    where isnull(OBJNAME,'') <>''

    Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

Viewing 5 posts - 1 through 4 (of 4 total)

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