How can I see progress within a transaction?
-
I have a series of sql statements in a stored procedure that move some data around. I have this all wrapped in a transaction, to ensure it all occurs. Then I proceed to delete some of the data, also within a transaction.
When this is launched, i am unable to detect progress until it is completed. I could write to a logging table, as the progress occurs, but since that is in a transaction, it will not show up until the end anyway. Unless I break up the processes. But thats the reason they are all in a transaction. All or nothing. Not a little bit, then log what happened, then a little more...
Maybe my approach is wrong. Help me out.
Can i write events to something that i can be reading to tell where my process is at?
I fired off the proc a bit ago, and after 10 minutes i killed it, thinking it was taking too long. Last night it had to run for nearly 3 hours. Granted, its hitting a lot of tables, and moving a lot of data.. but... I want to better know what is happening inside, to see if i goofed something up and i am the reason it is taking so long.
Long description i know, but i hope this helps someone out there guide me in the proper direction.
Thanks in advance.
-
Sorry I didn't respond sooner. But I was having trouble getting a cursor in the reply box when I was at work
If your process is running you can either use Profiler to see what it is doing or you can try querying the tables using the nolock hint. IE:
SELECT * FROM Foo WITH (NOLOCK)
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Here's a rearranged version of the popular SP_WHO2 which, when run with a specific PID, will show the sql statement being executed. At least mostly -- some things like UDF's, bulk operations and such do not show up.
It's not really polished, I've just been playing with it, but if you want to try it first run it as
DW_WHO2 ACTIVE
see which PID you are interested in, then do
DW_WHO2 123
And it will show basic stats for the process followed by the SQL statement being run. If running this from Query Analyzer, be sure to allow the per-column length on the results/options tab to be very long so you can see the whole SQL Statement.
Note this is unlike DBCC INPUTBUFFER which shows the whole batch; this shows the individual statement.
PS. This darn HTML editing is turning some code into icons, I edited to try to clean up, but some parenteses may not be quite right -- sorry about that -- blame this text entry tool that is trying to be "smart".
CREATE PROCEDURE dw_who2
@loginame sysname = NULL
as
--- SP_who2 modified by Levy to include SQL statement when specifying SPID
set nocount on
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.
--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--------
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char'
, sql_handle
, stmt_start/2 as stmt_start
, CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END as stmt_end
INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows?
IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh
--------Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
-- (Seems always auto sorted.) order by spid_sort
if ' + @charspidlow + ' = ' + @charspidhigh + '
begin
declare @stmt_start int
declare @stmt_end int
declare @sql_handle-2 binary(20)
select top 1 @stmt_start=stmt_start,
@stmt_end=stmt_end,
@sql_handle-2=sql_handle
from #tb1_sysprocesses
where spid between ' + @charspidlow + ' and ' + @charspidhigh + '
SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
 
FROM ::fn_get_sql(@sql_handle)
end
SET nocount on
'
)
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode -- sp_who2
-
DUH! how lame of me to not remember (nolock).
thanks.
Thanks also to Ferguson. Thanks as well for the code that was sent. I have not yet tried it, but look forward to doing so soon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply