Tracking a stored proc that is already running

Question

Post reply

Tracking a stored proc that is already running

Shark Energy

SSCrazy Eights

Points: 8389
More actions
March 20, 2008 at 4:35 am

#71468

Anyone know of a way of seeing what an sp is up to that is already running? Its been going for 24 hours, transferring millions upon millions of records. Want to see how far it has got or whether it is stuck, but nobody turned on our debug switch within the sp. Wondered if some dbcc command or profiler setting will help us?
Cheers

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

I find this really useful

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

We use this:

CREATE procedure sp_who3

(

@SPID smallint

)

AS

BEGIN

-- Code obtained from http://sqlblogcasts.com/blogs/antxxxx/archive/2006/11/30/script-to-find-out-what-a-spid-is-doing.aspx

SET NOCOUNT ON

DECLARE @sql_handle-2 binary(20), @handle_found bit

DECLARE @stmt_start int, @stmt_end int

declare @sqlstr nvarchar(4000)

dbcc inputbuffer(@spid)

--exec sp_who2 @spid

SET @handle_found = 0

SELECT@sql_handle-2 = sql_handle,

@stmt_start = stmt_start/2,

@stmt_end = CASE

WHEN stmt_end = -1 THEN -1

ELSE stmt_end/2

END

FROM master.dbo.sysprocesses

WHEREspid = @SPID

ANDecid = 0

SELECT substring(text, 0, 255) as Executing_Procedure

FROM ::fn_get_sql(@sql_handle)

SELECT SUBSTRING(text, COALESCE(NULLIF(@stmt_start, 0), 1),

CASE @stmt_end

WHEN -1 THEN DATALENGTH(text)

ELSE (@stmt_end - @stmt_start)

END

) as Executing_Statement

FROM ::fn_get_sql(@sql_handle)

end

GO

I created it in MASTER database so that anyone can use it againt any process and returns the current executing sql statement (the third output) for a given process id. It is not as comprehensive as the other post but something that I find useful.

Jez

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Jez: does that example work on SQL2000? I thought that the ::fn_get_sql function only worked on SQL2005.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 4

Its on SP3 onwards.

Thanks for your help peeps. I'm piecing it together.