Monitoring Connections to Databases

Question

Post reply

Monitoring Connections to Databases

kokon

SSC Rookie

Points: 48
More actions
August 17, 2010 at 7:48 am

#225960

Hi - I have a task to provide a list of applications that connect to one of my databases. I'd like to setup a monitor for a week time so that I have a window. I know I can go in to Activity Monitor and manually see this. But is there a built in funtion or a script I can write to be able to log all connections to this specific database? I'm running SQL 2005.
Thank you,
Kevin Okon
Sr. Systems Engineer
PCB Piezotronics, Inc.

Viewing 8 posts - 1 through 7 (of 7 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

you mean something like sp_who2

----------
Ashish

kokon SSC Rookie Points: 48 More actions · Answer 2

Yes sp_who2 is perfect. is there anything special I need to do?

Thanks! Sorry...I just started in the dba world!

crazy4sql SSCoach Points: 19590 More actions · Answer 3

sp_who2 will run for all the databases.

If its specific to one database then you can try something like :-

select * from sysprocesses where dbid = yourdatabaseid(which you can get by select * from master.sys.databases)

Also you can further filter this select for blocked <>0 (for blocking queries) or waittime > 0(for long running queries) and further.

----------
Ashish

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Well, that kinda works. It really doesn't guarantee that you are catching every connection to that database. That will provide you a list of all that are presently using the database but you may miss activity against that database.

Example, you could have a user do something like the following;

use master;

go

select * from yourdb.dbo.yourtable

...and you won't see that connected to your database in sysprocesses or sp_who2 but will see them instead connected to master.

So, if you are trying to collect all activity against that database then you will have to do something different. If just catching those that are directly using that database then you could periodically sample sp_who2. Again, you will still probably miss some as that is only a sample and not historical.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

crazy4sql SSCoach Points: 19590 More actions · Answer 5

then in that case I think we need to setup the auditing on every connection getting connected to database.

----------
Ashish

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 6

ashish.kuriyal (8/17/2010)
then in that case I think we need to setup the auditing on every connection getting connected to database.

Indeed. The only way to guarantee that you are getting all activity associated with a given database would to be to collect all select, insert, update, delete, procedure exec, etc for the whole instance and then analyze that data. There are several ways to go about doing that but this is something typically associated with SOX type compliance etc.

If there is any additional information on the purpose of the project that the OP can provide that would be helpful in recommending the most comprehensive solution.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

dhivamurugan SSC Rookie Points: 25 More actions · Answer 7

hai i am dhivakar i have some doubt on database connection.

how to connect visual basic 2008 to microsoft sql 2005 please tell me sir