Capture user activity for database - Any dmv's ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Capture user activity for database - Any dmv's ?

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-03-09 : 16:45:31

How do I capture user access to all different database as the management once to come up with a report to show by database , how many people are accessing it...similar to the below

For Month of March
Database A 10
Database B 7
Database C 5

Are there any dmv or system table that I can query to find this information ?

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-10 : 00:28:06

this can give u an idea of how many sessions are currently connected to a database. You can use this to maintain a history table in order to calculate for a given period.


select db_name(dbid) DatabaseName,count(spid) NumOfSessions
from sys.sysprocesses where dbid>4 --only user db
group by dbid

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-03-10 : 09:31:05

Thanks for your response,

Does the number of sessions get updated as users log in and log out?
How do I schedule this scripts to execute, so that I can continuosly capture the number of users for the database ?

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2010-03-11 : 00:33:19

quote:
Originally posted by LOOKUP_BI

Thanks for your response,

Does the number of sessions get updated as users log in and log out?
How do I schedule this scripts to execute, so that I can continuosly capture the number of users for the database ?

certainly, the sessions get updated as users log in and out.
You can run it as an sql agent job and save the results in a table.
you need to add date field to the table..something like


Create tblUserLog
(
Sno int identity,
DatabaseName varchar(25),
NumOfSessions int,
SnapshotDate Datetime
)

you can then group by Snapshot Date and figure out the sessions.

An alternative to this approach is the performance monitor.
you can schedule a counter log for the counter user connection, which I think is in sql server:general statistics section.
google perfmon for more info...it is relatively easy to configure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources