Posts: 0
Threads: 0
Joined: Mar 2019
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Actually, running `EXEC sp_who2` in Query Analyzer / Management Studio gives more info than `sp_who`.
Beyond that you could set up SQL Profiler to watch all of the in and out traffic to the server. Profiler also let you narrow down exactly what you are watching for.
For SQL Server 2008:
START - All Programs - Microsoft SQL Server 2008 - Performance Tools - SQL Server Profiler
Keep in mind that the profiler is truly a logging and watching app. It will continue to log and watch as long as it is running. It could fill up text files or databases or hard drives, so be careful what you have it watch and for how long.
|
Posts: 0
Threads: 0
Joined: May 2019
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
SELECT
p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
p.program_name, text
FROM
sys.dm_exec_requests AS r,
master.dbo.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
p.status NOT IN ('sleeping', 'background')
AND r.session_id = p.spid
|
Posts: 0
Threads: 0
Joined: May 2020
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
SELECT
p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, t.text
FROM
sys.dm_exec_requests as r,
master.dbo.sysprocesses as p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) t
WHERE
p.status NOT IN ('sleeping', 'background')
AND r.session_id = p.spid
And
KILL @spid
|
Posts: 0
Threads: 0
Joined: Feb 2021
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
You can use below query to find running last request:
SELECT
der.session_id
,est.TEXT AS QueryText
,der.status
,der.blocking_session_id
,der.cpu_time
,der.total_elapsed_time
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est
Using below script you can also find number of connection per database:
SELECT
DB_NAME(DBID) AS DataBaseName
,COUNT(DBID) AS NumberOfConnections
,LogiName
FROM sys.sysprocesses
WHERE DBID > 0
GROUP BY DBID, LogiName
For more details please visit:
[To see links please register here]
|
Posts: 0
Threads: 0
Joined: Jan 2022
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
As a note, the SQL Server Activity Monitor for SQL Server 2008 can be found by right clicking your current server and going to "Activity Monitor" in the context menu. I found this was easiest way to kill processes if you are using the SQL Server Management Studio.
|
Posts: 0
Threads: 0
Joined: Jul 2019
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
You should try very usefull procedure `sp_whoIsActive` which can be found here: [To see links please register here] and it is free.
|
Posts: 0
Threads: 0
Joined: Jun 2020
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
This will show you the longest running SPIDs on a SQL 2000 or SQL 2005 server:
select
P.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
121), 12) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER')
order by batch_duration desc
If you need to see the SQL running for a given spid from the results, use something like this:
declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)
set @spid = XXX -- Fill this in
select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from sys.sysprocesses
where spid = @spid
order by ecid
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)
|
Posts: 0
Threads: 0
Joined: Apr 2023
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Trying to put things together (hope to be helpful):
SELECT
p.spid,
RIGHT(CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, p.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS [batch_duration],
p.[program_name],
p.hostname,
MAX(p.loginame) AS loginame,
(SELECT SUBSTRING(text, COALESCE(NULLIF(spid.stmt_start, 0), 1) + 1, CASE spid.stmt_end WHEN -1 THEN DATALENGTH(text) ELSE (spid.stmt_end - spid.stmt_start) END) FROM ::fn_get_sql(spid.[sql_handle])) AS [sql]
FROM
master.dbo.sysprocesses p
LEFT JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY spid ORDER BY ecid) AS i,
spid,
[sql_handle],
CASE stmt_start WHEN 0 THEN 0 ELSE stmt_start / 2 END AS stmt_start,
CASE stmt_end WHEN -1 THEN -1 ELSE stmt_end / 2 END AS stmt_end
FROM sys.sysprocesses
) spid ON p.spid = spid.spid AND spid.i = 1
WHERE
p.spid > 50
AND p.status NOT IN ('background', 'sleeping')
AND p.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
GROUP BY
p.spid,
p.last_batch,
p.[program_name],
p.hostname,
spid.stmt_start,
spid.stmt_end,
spid.[sql_handle]
ORDER BY
batch_duration DESC,
p.spid
;
|
|