Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

combining outfrom sp_who2 and sysprocesses Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 5:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 27, 2014 1:27 PM
Points: 13, Visits: 131
hello,

Is there a way in whihc we can combine the out of sp_who2 with sysprocesses table in sql 2000.
need to find the longest running query in sql 2000 but no luck so far..

Any help is appreciated.

regards,
T
Post #1412965
Posted Tuesday, January 29, 2013 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
Try posting to the 2000 forum. You'll get answers more suited to SQL Server 2000 than posting in the 2008 forum.

To identify the longest running queries back in 2000, I used a server-side trace with trace events.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1412995
Posted Tuesday, January 29, 2013 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
wow SQL 2000;
edit : whoops this is clearly for 2005 or above; let me look again in my snippets; i know i have something

I have this proc saved in my snippets, which purports to identify what is SQl executing, and it does seem to grab an NTUsername;
see if this helps at all? you could search for "dba_WhatSQLIsExecuting" to find the origianl thread for more ideas.

CREATE PROC [dbo].[dba_WhatSQLIsExecuting]
AS
/*--------------------------------------------------------------------
Purpose: Shows what individual SQL statements are currently executing.
----------------------------------------------------------------------
Parameters: None.
Revision History:
24/07/2008 Ian_Stirk@yahoo.com Initial version
Example Usage:
1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting
---------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- What SQL Statements Are Currently Running?
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY 1, 2
END




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1412998
Posted Tuesday, January 29, 2013 6:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
ok here's one way to do it with SQL2000:
declare @sql_handle binary(20),@spid smallint;
declare c1 cursor for select sql_handle,spid from master..sysprocesses where spid >50;
open c1;
fetch next from c1 into @sql_handle,@spid;
while (@@FETCH_STATUS =0)
begin
select
spid,
login_time,
last_batch,
[status],
loginame,
a.text
from ::fn_get_sql(@sql_handle) a, master..sysprocesses b
where b.spid = @spid
fetch next from c1 into @sql_handle,@spid
end
close c1
deallocate c1;



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1413013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse