Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Monitoring Connections to Databases Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 7:44 AM
Points: 2, 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.
Post #970416
Posted Tuesday, August 17, 2010 7:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:10 AM
Points: 880, Visits: 4,092
you mean something like sp_who2

----------
Ashish
Post #970420
Posted Tuesday, August 17, 2010 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 7:44 AM
Points: 2, Visits: 1
Yes sp_who2 is perfect. is there anything special I need to do?

Thanks! Sorry...I just started in the dba world!
Post #970423
Posted Tuesday, August 17, 2010 8:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:10 AM
Points: 880, Visits: 4,092
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
Post #970436
Posted Tuesday, August 17, 2010 8:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:19 PM
Points: 2,107, Visits: 3,582
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #970475
Posted Tuesday, August 17, 2010 8:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:10 AM
Points: 880, Visits: 4,092
then in that case I think we need to setup the auditing on every connection getting connected to database.

----------
Ashish
Post #970485
Posted Tuesday, August 17, 2010 9:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:19 PM
Points: 2,107, Visits: 3,582
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #970487
Posted Saturday, April 6, 2013 11:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 6, 2013 10:55 PM
Points: 1, 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
Post #1439619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse