Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Find currently executing SQL statement

Author  Topic 

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-25 : 15:41:48
Here's a little script to find the currently executing statement. I put this together to get around a couple of limitations in DBCC INPUTBUFFER:

1) The 255 character output limit ... often I would get the "CREATE PROCEDURE" statement and the first couple of variable declarations, and that's it.
2) The fact that, in a multi-statement batch, you get the entire thing. This gives you the currently executing statement, not batch.

So, without further ado ...

/****************************************************/
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT

SET @SPID = <SPID to be examined>

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

SELECT text
FROM
::fn_get_sql (@Handle)
/****************************************************/

Enjoy!

I geek, therefore I am

dev2dev
Starting Member

48 Posts

Posted - 2008-04-03 : 08:34:26
This shows recent/last executed query??? NOT CURRENTLY/BEING EXECUTED query
Coz this shows the same query even after it completes execution

Correct me if I am wrong

We can achieve this using DMVs in sql server 2005
joining sys.dm_exec_requests and sys.dm_exec_sql_text will show the "currently" RUNNING query
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-03 : 23:20:25
Thats right!!!
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-04 : 13:41:44
It actually produces different output from dbcc inputbuffer - the dbcc command is usually the last client command executed but ::fn_get_sql can be executed from the server. It's useful as if a spid is stuck on a xp_sendmail for instance that is usually shown by the function.
I use them both here
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-04-14 : 12:48:16
2005 version, if anyone's interested:

DECLARE @SPID int -- The process ID you want the query for.
DECLARE @handle binary(20) -- The SQL Handle of the process.

SELECT @handle = [sql_handle] FROM [dbo].[sysprocesses] WHERE [SPID] = @spid
SELECT [Text] FROM [sys].[dm_exec_sql_text](@handle) -- SQL 2005 or later.
GO


____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide
Go to Top of Page

hey001us
Posting Yak Master

185 Posts

Posted - 2008-07-29 : 17:47:08

SQL 2005 Version:

SELECT @@SERVERNAME SvrName, s.[Status], s.SPID, s.CPU, s.Physical_IO, s.WaitTime, s.Blocked,
Text LastCmd, SUBSTRING(s.HostName, 1, 36) HostName, SUBSTRING(s.Program_Name, 1, 100) PrgName,
SUBSTRING(s.nt_username, 1, 50) NtUserName, s.Login_Time, s.Last_Batch, s.Open_Tran
FROM master.dbo.sysprocesses s
CROSS APPLY ::fn_get_sql(s.sql_handle)
WHERE (blocked > 0
OR spid IN (SELECT blocked FROM master.dbo.sysprocesses (NOLOCK) WHERE blocked > 0)
OR open_tran > 0)
AND SPID <> @@SPID
AND Blocked > 0


hey
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 07:24:55
A combined version....


/* Jobs Details for a specific db (works on 2000 and 2005)
**
** Charlie (19/02/2008)
** Modified (11/06/2008)
*/

-- ==== Configuration =========================================================

DECLARE @dbName VARCHAR(50) SET @dbName = '<your dbname here>'

-- ==== ------------- =========================================================

USE MASTER
SET NOCOUNT ON

IF object_Id('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
IF object_Id('tempdb..#info') IS NOT NULL DROP TABLE #info
IF object_Id('tempdb..#dbccInput') IS NOT NULL DROP TABLE #dbccInput

DECLARE
@spid INT
, @sqlHandle BINARY(20)
, @sqlVersion INT

-- Which version of SQL server are we running.
SET @sqlVersion = CASE
WHEN @@VERSION LIKE '%2005 - 9.%' THEN 2005
WHEN @@VERSION LIKE '%2000 - 8.%' THEN 2000
END

CREATE TABLE #spWho (
[SPID] INT NOT NULL
, [Status] VARCHAR (255) NOT NULL
, [Login] VARCHAR (255) NOT NULL
, [HostName] VARCHAR (255) NOT NULL
, [BlkBy] VARCHAR(10) NOT NULL
, [DBName] VARCHAR (255) NULL
, [Command] VARCHAR (255) NOT NULL
, [CPUTime] INT NOT NULL
, [DiskIO] INT NOT NULL
, [LastBatch] VARCHAR (255) NOT NULL
, [ProgramName] VARCHAR (255) NOT NULL
, [SPID2] INT NOT NULL
)

-- Add a column if it's 2005
IF @sqlVersion = 2005 ALTER TABLE #spWho ADD [REQUESTID] INT NOT NULL DEFAULT 0

CREATE TABLE #dbccInput (
eventType NVARCHAR(30)
, paramaters INT
, eventInfo NVARCHAR(4000)
)

CREATE TABLE #info (
[spid] INT NOT NULL
, [status] VARCHAR(20) NOT NULL
, [login] VARCHAR(255) NOT NULL
, [blkBy] VARCHAR(10) NOT NULL
, [command] VARCHAR(255) NOT NULL
, [CPUTime] INT NOT NULL
, [DiskIO] INT NOT NULL
, [LastBatch] VARCHAR (255) NOT NULL
, [ProgramName] VARCHAR (255) NOT NULL
, [sqlHandle] BINARY(20) NULL
, [eventInfo] NVARCHAR(4000) NULL
, [sql] TEXT NULL
)

INSERT INTO #spWho EXEC sp_who2

-- Get basic job info for the database we are intersted in
INSERT INTO #info (
[spid]
, [status]
, [login]
, [blkBy]
, [command]
, [CPUTime]
, [DiskIO]
, [LastBatch]
, [ProgramName]
, [sqlHandle]
)
SELECT
sw.[spid]
, sw.[status]
, sw.[login]
, sw.[blkBy]
, sw.[command]
, sw.[CPUTime]
, sw.[diskIO]
, sw.[lastBatch]
, sw.[programName]
, sp.[sql_handle]
FROM
#spWho sw
JOIN master.dbo.sysprocesses sp ON sp.[spid] = sw.[spid]
WHERE
[DBName] = @dbName

-- Get the sql info for each spid
DECLARE sqlCursor CURSOR LOCAL READ_ONLY FOR
SELECT
i.[spid]
, i.[sqlHandle]
FROM
#info i

OPEN sqlCursor

FETCH NEXT FROM sqlCursor INTO
@spid
, @sqlHandle

WHILE (@@fetch_status = 0) BEGIN

INSERT INTO #dbccInput
EXEC ('DBCC INPUTBUFFER(' + @spid + ')')

UPDATE #info
SET
[eventInfo] = dbccI.[eventInfo]
FROM
#dbccInput dbccI
WHERE
[spid] = @spid

-- clear the input info
TRUNCATE TABLE #dbccInput

-- Get the sql full text (only does currently running process)
UPDATE #info
SET
[sql] = fgs.[text]
FROM
::fn_get_sql(@sqlHandle) fgs
WHERE
[spid] = @spid

FETCH NEXT FROM sqlCursor INTO
@spid
, @sqlHandle

END

CLOSE sqlCursor
DEALLOCATE sqlCursor

-- Display the results
SELECT
[spid]
, [status]
, [login]
, [blkBy]
, [command]
, [CPUTime]
, [DiskIO]
, [LastBatch]
, [ProgramName]
, [eventInfo] AS lastEvent
, [sql] AS currentSql
FROM
#info

IF object_Id('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
IF object_Id('tempdb..#info') IS NOT NULL DROP TABLE #info
IF object_Id('tempdb..#dbccInput') IS NOT NULL DROP TABLE #dbccInput


-------------
Charlie
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-07 : 07:40:38
this works too
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page
   

- Advertisement -