January 23, 2003 at 9:21 am
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!
January 23, 2003 at 11:01 am
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
January 23, 2003 at 2:29 pm
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
January 24, 2003 at 9:39 am
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.
January 24, 2003 at 12:39 pm
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