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
 General SQL Server Forums
 New to SQL Server Administration
 troubleshooting performance

Author  Topic 

farrokhp
Starting Member

15 Posts

Posted - 2011-03-10 : 04:29:39
in sql 2005, I want to know activities on the server for each database of the server...i.e. I want to list top 10 users in each database which hammering the database most and what they are doing...can you give me a script to do that?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-10 : 08:11:22
How to get active users list
SELECT
DISTINCT
name AS database_name,
session_id,
host_name,
login_time,
login_name,
reads,
writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> 'DATABASE'--AND name ='YourDatabaseNameHere'
ORDER BY writes,reads


--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

farrokhp
Starting Member

15 Posts

Posted - 2011-03-12 : 08:08:25
Thanks, it is close but I am looking for loop through all the databases and list result by database , database ...is it possible in one script?
Go to Top of Page
   

- Advertisement -