Tracking count of users in a DB over time
-
We're trying to track SQL server users as a count of the total of logged in users in a particular DB. Basically, we have a 3rd-party app that requires expensive, per-user concurrent licensing and I'm trying to do Hurst-like time series analysis to figure out the optimal number of licenses to buy.
(FWIW, I've been in charge of SQL2000 here for a while but that has never involved any substantial amount of work. For all intents, I'm a newbie).
To do this, what I need would be something like what's in Enterprise Manager's Management\Current Activity\Process Info, sampled at 30 minute intervals, and just giving a count of the total number of users for database='TMS' (that's the name of the DB in question). Then the results of that count would be written to a time-stamped line in a text file, or to a table, each 30 minutes.
I know how to set this up as a scheduled job, if I can set up the job correctly. That's what I don't know how to do :-).
What I have come up with on my own is a trace in SQL profiler that tracks login and logout where databaseName is like 'TMS.' This is pretty good, but then I'll have to do some magic on the file to figure out concurrent users at any specific time. Not quite sure how I'm going to do that. So I think the other way would probably be better.
Thanks for any ideas!
--Matt
-
Look at the sp_who or sp_who2 (this one is undocumented but gives more information) stored procedures - they give a wealth of information about current activity in the database.
Also, you could run profiler whilst looking at the stats you want from EM, after all it is just executing SQL statements that you yourself can execute. Watch what it does and pull out the commands you want.
I'd be interested in seeing how you analyse the data
-
Hi Matt,
What have you ended up doing to collect this data? Having to do something similar myself at the moment.
Cheers,
Rob.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply