How to find active Transactions in Query Analyzer
-
Dear All Guru's
How to find active Transactions from Query Analyzer.
Any help would be greatly appreciated.
Regds
Dharmendra
Thanks & Regards
Dharmendra S Mudaliar
( OEM India RSA )
Hello - +91 40 66934555 x 34555
Mobile - 9885408049 | IM - v-dharmu@microsoft.com -
DBCC OPENTRAN might be a start
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Another way would be to run "sp_who2 active" and then you could run DBCC INPUTBUFFER(SPID # here) to see the transactions....
I know you can output the 1st one to a table but am unsure about the 2nd...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Here's a script I saw on SSWUG (apologies to the Author for not citing it but it her/his name may not have been in the comments):
***************************************
create PROCEDURE sp_Diagnostic_ActiveDBProcessStatus @db varchar(255)AS
-- Script downloaded 1/16/2004 1:36:21 PM
-- From The SQL Server Worldwide User's Group (http://www.sswug.org)
-- Scripts provided AS-IS without warranty of any kind use at your own risk
--
-- Show active SQL users
--
-- Similar to sp_who, but
-- shows only connections that
-- are actively doing something
-- at this instant in time
-- (sleeping processes are not listed).
--
-- Also shows last SQL statement issued
-- by each connection.
--
SET NOCOUNT ON
DECLARE
@Cnt int,
@Msg varchar(255),
@ActiveCnt int,
@TotalCnt int,
@spid int,
@Cmd varchar(255),
@SQLUser varchar(25), @DBName varchar(20), @Pgm varchar(24)
select @Msg = 'Active connections on ' + @@ServerName + ' as of ' + convert(varchar(30),getdate(),109) + ':'
print @Msg
print ''
CREATE TABLE #t(spid int, HostName varchar(128), Status varchar(12), SQLUser varchar(25), DBName varchar(50), Cmd varchar(16), BlkBy varchar(3), ProgramName varchar(24),
WaitReason varchar(50), CPUTime int, DiskIO int, Last_Batch datetime, WaitType int, HostProcess varchar(8), NTUser varchar(128), NTDomain varchar(128), login_time datetime,
net_address varchar(12), NetLibrary varchar(50))
insert into #t
select spid as 'spid',
convert(varchar(128), HostName) as 'HostName',
Convert(varchar(12),Status) as 'Status',
convert(varchar(25),loginame) as 'SQLUser',
substring(db_name(dbid),1,20) as 'DBName',
Cmd as 'Cmd',
Case when blocked is null or blocked = 0 then ' .'
else convert(char(3),blocked)
end as 'BlkBy',
convert(varchar(24), program_name) as 'ProgramName',
Case WaitType
When 0x0 Then ' '
When 0x0005 Then 'Exclusive Page Lock'
When 0x0012 Then 'Table Lock'
When 0x0013 Then 'Exclusive Buffer Lock'
When 0x0020 Then 'Disk I/O'
When 0x0022 Then 'Buffer Being Dirtied'
When 0x13 Then 'Exclusive Buffer Lock'
When 0x23 Then 'Buffer Being Dump'
When 0x5 Then 'Exclusive Page Lock'
When 0x6 Then 'Shared Page Lock'
When 0x800 Then 'Network I/O'
When 0x8001 Then 'Exclusive Table Lock'
When 0x8003 Then 'Exclusive Intent Lock'
When 0x8004 Then 'Shared Intent Lock'
When 0x8005 Then 'Exclusive Page Lock'
When 0x8006 Then 'Shared Page Lock'
When 0x8007 Then 'Update Page Lock'
When 0x8011 Then 'Shared Buffer Lock'
When 0x81 Then 'Write Log'
Else 'Unknown'
End as 'WaitReason',
convert(int,cpu) as 'CPUTime',
convert(int,physical_io) as 'DiskIO',
Last_Batch,
WaitType,
HostProcess,
nt_username as 'NTUser',
CASE WHEN nt_domain = '' THEN ' .'
ELSE nt_domain
END as 'NTDomain',
login_time,
net_address,
case net_library
when 'SSNMPN70.DLL' then 'Named Pipes'
when 'SSMSSO70.DLL' then 'TCP/IP'
when 'SSMSRP70.DLL' then 'Multi Protocol'
when 'SSMSSP70.DLL' then 'NWLink IPX/SPX'
when 'SSMSAD70.DLL' then 'Apple Talk'
when 'SSMSVI70.DLL' then 'Banyan Vines'
else net_library
end as 'NetLibrary'
from master.dbo.sysprocesses p
where db_name(dbid) like @db
and (Status not in ('Sleeping','Background') -- this one is runnable
or Cmd = 'WAITFOR' -- or this one is doing a 'WAITFOR' command
or blocked > 0 -- or blocked
or waittype <> 0x0000
or exists ( select * -- or blocking
from master..sysprocesses p2
where p.spid <> p2.spid
and p.spid = p2.blocked
)
 
select @ActiveCnt = @@rowcount
select @TotalCnt = count(*) from master..sysprocesses
select @Msg = @@ServerName + ' has ' + convert(varchar(10),@TotalCnt) + ' SQL Server connections, with ' + convert(varchar(10),@ActiveCnt) + ' active.'
print @Msg
print ''
declare spidCsr cursor for
Select spid, rtrim(SQLUser), rtrim(ProgramName), rtrim(DBName)
from #t
order by spid
DECLARE @ExecStr varchar(50), @Qry nvarchar(255)
CREATE TABLE #qinputbuffer
(
spid int,
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
 
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
 
open spidCsr
While 1 = 1
begin
fetch spidCsr into @spid, @sqluser, @Pgm, @DBName
if @@fetch_status <> 0 break
print '****************************************************************************************************************************'
select @Msg = 'spid ' + convert(varchar(10),@spid) + ': SQLUser=' + @sqluser +
', DB=' + @DBName + ', Pgm=' + @Pgm
print @Msg
print ''
-- select @Cmd = 'dbcc inputbuffer(' + convert(varchar(10),@spid) + ')'
-- exec (@Cmd)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@spid) + ')'
TRUNCATE TABLE #inputbuffer
INSERT INTO #inputbuffer
EXEC (@ExecStr)
INSERT INTO #qinputbuffer SELECT @spid, EventType, Parameters, EventInfo FROM #inputbuffer
end
close spidCsr
deallocate spidCsr
select convert(varchar(255), #qinputbuffer.EventInfo) as EventInfo,
#t.*, convert(varchar(30), #qinputbuffer.EventType) as EventType,
#qinputbuffer.Parameters, #t.spid
from #t
left outer join #qinputbuffer on #qinputbuffer.spid=#t.spid
order by WaitReason, ProgramName, Last_Batch, SQLUser, HostProcess, #t.SPID
drop table #t
drop table #inputbuffer
drop table #qinputbuffer
G. Milner
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply