Users: Are they active?

Question

Users: Are they active?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

GilaMonster (9/23/2008)
On a fairly active server I used to work with, on SQL 2000 we were writing a 200MB C2 file about every 15 min. After we upgraded to 2005, we were writing a 200MB file about every 20 sec.

DAAANNGGG!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jeff Moden (9/23/2008)
GilaMonster (9/23/2008)
On a fairly active server I used to work with, on SQL 2000 we were writing a 200MB C2 file about every 15 min. After we upgraded to 2005, we were writing a 200MB file about every 20 sec.
DAAANNGGG!

You said it. iirc the server went down a few times while we figured out what was happening and got the trace stopped.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 3

fjmorales (9/23/2008)
I used the query for a server on SQL Serv 2005 and until now its been greate but teh server that I whant to verify is in 2000.
Is it possible to have a query like taht on SQL Server 2000?

No, for sql2000 there are no login triggers.

For SQL2000 you're stuck to a trace or to a sample job.

As already stated, C2 may generate way to much for your needs.

However, you can launch your own trace, just logging Audit Login trace events.

check the attached file as an example...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

fjmorales Default port Points: 1451 More actions · Answer 4

fjmorales

Default port

Points: 1451

September 24, 2008 at 4:13 pm

#876719

thanks! I che it out.

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 5

generally understood sequence is

client application makes new connection

SQL engine authenticates client [as LOGIN], and allocates a SPID

subsequent TSQL sent to SPID

SPID checks authorisation [as USER] for db/object access

so although there is no simple facility (except Profiler as previously suggested) to track object access, at least you can new use LOGIN TRIGGER facility (introduced with SQL2005-SP2) to track those pre-requisite logins

for more info see this BOL write-up

http://msdn.microsoft.com/en-us/library/bb326598(SQL.90).aspx

HTH

Dick

jlkron SSC Veteran Points: 238 More actions · Answer 6

Hi fjmorales,

We have many developer databases on each of our SQL Servers and we do something like what you are looking for to determine which databases are inactive so we can delete them. The same concept should work for users in a single database, though.

We created a small utility database with a DBUSAGE table to capture the output.

Then, we created a SQL Server job that runs a stored procedure that queries the master..sysprocesses table every 30 minutes or so to look at the current activity on the server. The frequency that this job needs to run would probably vary in different environments.

The stored procedure will either insert new rows or update existing rows in the DBUSAGE table for each of the databases that have open connections. There is a column called LastConnectTime which gets set to the current datetime.

Every couple of months we go through this table and delete databases that have had no activity in 90 days or so.

--John

--- Edit 09/25 ---

My apologies... :blush:

I just noticed that homebrew01 provided essentially the same suggestion in an earlier post.

I can verify that it works well for our needs, however.

--John

--- End Edit 09/25 ---

fjmorales Default port Points: 1451 More actions · Answer 7

Yes, Im trying to make some time to make some time to see what can I come up with. For I became the DBA for the company with like 30 dbs but I still am the predictive dialer administrator too.