June 6, 2008 at 7:40 am
Hi
I am currently working in SQL 2000. Except for DBCC INPUTBUFFER, are there any other function/table that displays the current SQL statement a specific spid is executing?
Thanks
Telammica
June 6, 2008 at 12:07 pm
To capture the Sql executed by a spid, get the spid's handle from sysprocesses, then pass into table-valued function fn_get_sql in master. Note, if the handle no longer exists in cache, then fn_get_sql will not return anything. Also, I think fn_get_sql is available only in SP4 for 2000.
DECLARE @bHandle binary(20), @Sql varchar(8000), @Spid smallint
SELECT @bHandle = sql_handle FROM master..sysprocesses WHERE spid = @Spid
IF @@ROWCOUNT > 0
SELECT @cSql = (SELECT Cast([text] as varchar(8000))) FROM ::fn_get_sql(@bHandle)
June 10, 2008 at 3:27 am
That user defined function fn_get_sql does not exist in the current version of SQL2000 I have installed, SP2 🙁 It would have been great cause then i could have gained select access for it and my problem would have been solved. Upgrading withing the next few months is out of the question.
Is there any other way of using access control on a user to use DBCC INPUTBUFFER. The problem is that I cannot gain sysadmin rights, yet I have to use the sql for a number of spids.
Is there any other suggestions?
June 10, 2008 at 7:14 am
Sorry, I do not know of another way.
June 10, 2008 at 10:23 am
I am on version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
and I use the ::fn_get_sql all the time and it has always worked for me.
Here is the code that I use:
USE MASTER
Declare @handle as binary(20)
select @handle = sql_handle from sysprocesses where spid = '95'
select @handle
select * from ::fn_get_sql(@handle)
June 10, 2008 at 10:27 am
After further review this is service pack 4, oops. I should have realized this.
June 10, 2008 at 11:52 pm
i dont understand prety much what you really want to do. can you explain exactly what you need?
if what you need is that a common user can see the SQL statement of another users. you could make a trigger or an sp that automatically insert in a table the SQL statement obtained from DBCC INPUTBUFFER for each user because each user can see their own SPID.
So later any user could see that table.
PS: sorry for my bad english :hehe:
June 11, 2008 at 1:48 am
Let me explain what I want to do. I have a table that gets updated with a bunch of system info, including spids. I need to update the SQL text as for each of the spids. This proc will be called every 5 seconds. Only the DBA has sysadmin access, therefore he needs to grant me exec or read permissions to some process/table. DBCC INPUTBUFFER needs sysadmin access, so I cannot use that. SQL 2005 has some very nice new features, but the best I can do now is work on Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Mar 9 2006 11:38:51. When I use the function that you guys mention, ::fn_get_sql () it doesn't return anything. That is not suppose to happen right?
USE MASTER
Declare @handle as binary(20)
select @handle = sql_handle from sysprocesses where spid = '61'
select @handle
select * from ::fn_get_sql(@handle)
------------------------------------------
0x010005000BB89439C0A0B3350000000000000000
(1 row(s) affected)
dbid objectid number encrypted text
------ ----------- ------ ---------
(0 row(s) affected)
Is there anything else that I need to set up first for it to return results?
June 11, 2008 at 6:40 am
Permissions (From books online)
DBCC INPUTBUFFER permissions default to members of the sysadmin fixed server role only, who can see any SPID. Other users can see any SPID they own. Permissions are not transferable.
according to this any user can use DBCC INPUTBUFFER for their own SPID... so perhaps you could SAVE the result of DBCC INPUTBUFFER into that table instead of just saving the SPID's.
June 11, 2008 at 3:01 pm
Why not have the process that runs every 5 seconds (ouch) run by a sysadmin user and then simply grant read access to the table to the users that need to look at the data?
Can you take a step back and explain what the purpose of this process is for? There may be a better way to do this, but we'll need to understand what you are going to use the data for to be of more help.
June 12, 2008 at 8:47 am
We have major problems on blocking processes. It is difficult to pinpoint exactly which processes/sps are causing the locks. I think it might be query optimizer that chooses a table scan on an update - then the other processes wait for that update to complete. I need to optimize the queries to remove the blocks that it causes. Some of them causes wait locks for up to 4 seconds, which causes hell in other applications. This is an OLTP environment, clustered on a SAN. I have created a sp that reads from sysprocesses, syslock and syslockinfo on production db, with DBCC INPUTBUFFER. I only select out blocked processes, with their locks, as well as the SQL text to identify which query it is and which object holds the lock. This query runs every 5 secs to catch the blocks whenever they happen and updates the waittime, CPU and I/O in 2 permanent tables on the database. It is cleared every day, with a summary of the information moving to a history table. I did not have sufficient infrastructure on production, and I needed to create a webapp so that all the other developers can have access to this info. The overhead of this sp of mine is minute. Problem was that I had to move the process of mine to another server on the SAN, so I created a linked server to the prod, but I only have read-only access to the prod db. Now I created a job that every 5 seconds get the data from 3 system tables from prod to my database, then process and store stats and history on my database as well as run the webapp from there. That means i cannot execute DBCC INPUTBUFFER anymore since read only access cannot be granted to that. Therefore I need a process that can be granted read only access on, and fn_get_sql seemed like the ideal udf for that. That is the only 2 functions on SQL 2000 that I know of that return the SQL. The only problem I have is that due to company policy, I can only have read only access for my process. The sps are called from +- 15 databases.
June 12, 2008 at 10:14 am
It may interest you to know that you can get fn_get_sql from Microsoft without upgrading to SP3. See here: http://support.microsoft.com/kb/325607/en-us
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply