Monitoring Connections to Databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Monitoring Connections to Databases

Monitoring Connections to Databases

Rate Topic

Display Mode

Topic Options

Author

Message

kokon

Posted Tuesday, August 17, 2010 7:48 AM

Forum 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

crazy4sql

Posted Tuesday, August 17, 2010 7:51 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 876, Visits: 3,742

you mean something like sp_who2

----------
Ashish

Post #970420

kokon

Posted Tuesday, August 17, 2010 7:55 AM

Forum 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

crazy4sql

Posted Tuesday, August 17, 2010 8:14 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 876, Visits: 3,742

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

David Benoit

Posted Tuesday, August 17, 2010 8:46 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 2,065, Visits: 3,453

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

crazy4sql

Posted Tuesday, August 17, 2010 8:55 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 876, Visits: 3,742

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

----------
Ashish

Post #970485

David Benoit

Posted Tuesday, August 17, 2010 9:01 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 2,065, Visits: 3,453

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

dhivamurugan

Posted Saturday, April 06, 2013 11:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, April 06, 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 »

Permissions