Activity Monitor Vs Login Vs Database vs host name
-
How to get the Database details with login user in SQl Server 2008.
In 2005 by the activity monitor we can able see login with using Database and host details.
In 2008 activity monitor is not showing database details.
Any help how to get these? is there any alternative?
Thansk,
Kishore KK
-
Not sure what you mean
It does show DATABASE in the "Processes" panel in SSMS 2008 for me, and others
SQLServerNewbieMCITP: Database Administrator SQL Server 2005 -
Thanks for the update.
In your Image just look there is a Login "sa" is not showing connected database information.
That is what i need. If i want to kill few threads which is connected with perticular database then how? by the using activity monitor.
Note: Please compare both 2005 and 2008 activity monitors with Login, database columns.
Thanks,
KK
-
koteswara kishore K. (3/18/2009)
Thanks for the update.In your Image just look there is a Login "sa" is not showing connected database information.
That is what i need. If i want to kill few threads which is connected with perticular database then how? by the using activity monitor.
Note: Please compare both 2005 and 2008 activity monitors with Login, database columns.
Thanks,
KK
Activity Monitor won't be a quick way to manually find and kill processes anyway
Select from sysprocesses and filter by DB
DECLARE @SQL VARCHAR(2000)
SELECT @SQL = COALESCE(@SQL, '') + 'Kill ' + CAST(spid AS VARCHAR(10)) + '; '
FROM sysprocesses
WHERE
dbid = DB_ID('master')
AND spid > 50-- not system process
AND spid <> @@spid
PRINT @SQL
--EXEC (@SQL)
SQLServerNewbieMCITP: Database Administrator SQL Server 2005 -
Database names not listed except system databases.
What is wrong on the activity monitor ?
if you execute exec sp_who yu will see the old way but we would like to know why at 2008 actv. mon doesnt show ?
i have found usefull link explain the case
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=386635
-
Jerry Hung (3/18/2009)
koteswara kishore K. (3/18/2009)
Thanks for the update.In your Image just look there is a Login "sa" is not showing connected database information.
That is what i need. If i want to kill few threads which is connected with perticular database then how? by the using activity monitor.
Note: Please compare both 2005 and 2008 activity monitors with Login, database columns.
Thanks,
KK
Activity Monitor won't be a quick way to manually find and kill processes anyway
Select from sysprocesses and filter by DB
DECLARE @SQL VARCHAR(2000)
SELECT @SQL = COALESCE(@SQL, '') + 'Kill ' + CAST(spid AS VARCHAR(10)) + '; '
FROM sysprocesses
WHERE
dbid = DB_ID('master')
AND spid > 50-- not system process
AND spid <> @@spid
PRINT @SQL
--EXEC (@SQL)
I'm pretty sure the processes you are referring to are background/system processes which you couldn't kill anyway 🙂 Here is the T-SQL for activity monitor in 2008. I'm sure you could alter to suite your specific needs.
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0)--,
--[Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
--LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id) -- SUPPORTED IN 2008
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id;
Tommy
Follow @sqlscribe -
Thanks for the query. Does anyone know where the "Database I/O" graph gets its information? Is its OS Perf counter? or maybe it does a refresh on some of the dynamic views showing only whats changed in the last refresh period?
Sean
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply