SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Connections to Databases


Monitoring Connections to Databases

Author
Message
kokon
kokon
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1
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.
crazy4sql
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4125 Visits: 4514
you mean something like sp_who2

----------
Ashish
kokon
kokon
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1
Yes sp_who2 is perfect. is there anything special I need to do?

Thanks! Sorry...I just started in the dba world!
crazy4sql
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4125 Visits: 4514
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
David Benoit
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7870 Visits: 3650
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
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4125 Visits: 4514
then in that case I think we need to setup the auditing on every connection getting connected to database.

----------
Ashish
David Benoit
David Benoit
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7870 Visits: 3650
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
dhivamurugan
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 0
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search