SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find currently executing SQL statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PABluesMan
Starting Member

26 Posts

Posted - 03/25/2008 :  15:41:48  Show Profile  Reply with Quote
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

India
48 Posts

Posted - 04/03/2008 :  08:34:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/03/2008 :  23:20:25  Show Profile  Reply with Quote
Thats right!!!
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/04/2008 :  13:41:44  Show Profile  Visit nr's Homepage  Reply with Quote
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

USA
123 Posts

Posted - 04/14/2008 :  12:48:16  Show Profile  Visit readysetstop's Homepage  Send readysetstop a Yahoo! Message  Reply with Quote
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 - 07/29/2008 :  17:47:08  Show Profile  Reply with Quote

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

Edited by - hey001us on 07/29/2008 18:11:59
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/07/2008 :  07:24:55  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 08/07/2008 :  07:40:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000