SSMS Activity Monitor - any way to export, print or save results?

Question

Is there any way to export, save or print (not screen prints, thanks) the output of the Activity Monitor?  Same question about JOB Activity Monitor, too.

 

Thanks,

 

Jack

 

Answer 1

Well, if you don't do filtering, and you just want the grid:  Activity monitor runs the following query:

 

Code Snippet

-- cleanup

if (object_id(N'tempdb..#snapshot_processinfo') is not null)

exec(N'drop table #snapshot_processinfo')

if (object_id(N'tempdb..#snapshot_lockinfo' ) is not null)

exec(N'drop table #snapshot_lockinfo' )

if (object_id(N'tempdb..#snapshot_processinfofltr') is not null)

exec(N'drop table #snapshot_processinfofltr')

 

-- build snapshot containing info about processes #snapshot_processinfo

select

[Process ID] = p.spid,

[IsSystemProcess] = case when s.is_user_process = 1 then 0 else 1 end,

[User] = p.loginame, [Database] = ISNULL(db_name(p.dbid),N''),

[Status] = p.status,

[Open Transactions] = p.open_tran,

[Command] = p.cmd,

[Application] = p.program_name,

[Wait Time] = p.waittime,

[Wait Type] = case when p.waittype = 0

then N''

else p.lastwaittype

end,

[Wait Resource] = case when p.waittype = 0

then N''

else p.waitresource

end,

[CPU] = p.cpu,

[Physical IO] = p.physical_io,

[Memory Usage] = p.memusage,

[Login Time] = p.login_time,

[Last Batch] = p.last_batch,

[Host] = p.hostname,

[Net Library] = p.net_library,

[Net Address] = p.net_address,

[Blocked By] = p.blocked,

[Blocking] = 0,

[Execution Context ID] = p.ecid,

[ID] = IDENTITY(int, 1, 1),

[ROW ID] = 0

into #snapshot_processinfo

from master.dbo.sysprocesses p, master.sys.dm_exec_sessions s

with (NOLOCK)

where p.spid = s.session_id

order by p.spid

 

SELECT * FROM #snapshot_processinfo

 

 

It runs a series of other queries for LockInfo that are database instance specific, and I haven't figured out how to write that into a dynamic statement yet.  Not sure if you need that.  The Job Activity Monitor is:

 

Code Snippet

create table #tmp_sp_help_category

(category_id int null, category_type tinyint null, name nvarchar(128) null)

insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category;

SELECT

sv.name AS [Name],

CAST(sv.enabled AS bit) AS [IsEnabled],

tshc.name AS [Category],

sv.category_id AS [CategoryID],

tshc.category_type AS [CategoryType],

null AS [CurrentRunStatus],

null AS [CurrentRunStep],

null AS [HasSchedule],

null AS [HasStep],

null AS [HasServer],

null AS [LastRunDate],

null AS [NextRunDate],

null AS [LastRunOutcome],

CAST(sv.job_id AS nvarchar(100)) AS [job_id],

tshj.*

FROM

msdb.dbo.sysjobs_view AS sv

INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_id

drop table #tmp_sp_help_category

 

 

joined to the output of

 

Code Snippet

exec msdb.dbo.sp_help_job

 

 

 

which can not be placed into a table due to nested insert EXEC rules.  I tried to trace it all out in profiler and lost interest for tonight.  I will play with it some tomorrow though.

Answer 2

Thanks, that does get me what I need - at least for that particular output.  But in SQL 2000, there used to be an "export list" feature that seems to have been taken out of SQL 2005.  That was so much easier!

 

Thanks again,

 

 

Jack

 

Answer 3

What if your connected to a 2000 server? The master.sys.dm_exec_sessions table doesn't exist.

Answer 4

True.. This script will not work in 2000. WHat you can do is, run a profiler in 2000 and then open Activity Monitor. YOu will get the query running behind. I am not pasting that query here it is 140+ line query. So i would request you to run yourself and see

 

Madhu