SQL 2005 ACTIVITY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL 2005 ACTIVITY

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-06-05 : 11:11:22

I usually run this command in SQL 2000.
Which tells me what is running with all the sql code statements.

Is there an alternative command to run in SQL 2005...rather than using the Reports - i find it easier to execute sp i called this sp_now.

set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500)

declare ActiveSpids CURSOR FOR
select sql_handle, spid
from sysprocesses
where sql_handle <> 0x0000000000000000000000000000000000000000
--and spid <> @@SPID
order by cpu desc

OPEN ActiveSpids
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid

set @rowcnt = @@CURSOR_ROWS

print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print ' '
set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)
print @output

WHILE (@@FETCH_STATUS = 0)
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '

select 'loginame' = left(loginame, 30),
'hostname' = left(hostname,30),
'datagbase' = left(db_name(dbid),30),
'spid' = str(spid,4,0),
'block' = str(blocked,5,0),
'phys_io' = str(physical_io,8,0),
'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END ,
'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
'program_name' = left(program_name,50),
'command' = cmd,
'lastwaittype' = left(lastwaittype,15),
'login_time' = convert(char(19),login_time,120),
'last_batch' = convert(char(19),last_batch,120),
'status' = left(status, 10),
'nt_username' = left(nt_username,20)
from master..sysprocesses
where spid = @spid
print ' '
print ' '

-- Dump the inputbuffer to get an idea of what the spid is doing
dbcc inputbuffer(@spid)
print ' '
print ' '

-- Use the built-in function to show the exact SQL that the spid is running
select * from ::fn_get_sql(@handle)

FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
END
close ActiveSpids
deallocate ActiveSpids

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-05 : 11:21:45

I would rather go with Standard Reports which comes with SQL server which gives me blocking information and Top CPU intensive queries.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-05 : 22:51:29

Replace master..sysprocesses with master.sys.sysprocesses then try.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-06-06 : 09:59:01

Thanks got it working.....

122319fs
Starting Member

1 Post

Posted - 2008-08-19 : 14:59:46

can you help please.

i run the sp_now but only get a repsonse for 1 or 2 users

i'm expecting many more

can you help me - maybe i've done something wrong.

thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-19 : 23:48:13

What's sp_now? What kind of permission do you have on sql server?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources