Captures all server and processes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Captures all server and processes

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-28 : 17:43:52

Hi ,

I want to capture all Processes,Applications and Servers (name) which is accessing(linking) to my server. Is it Possible without using Profiler?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:45:48

sp_who
sp_who2
aba_lockinfo

E 12°55'05.25"
N 56°04'39.16"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-28 : 17:47:32

Peso,

I know that System stored procedure. But i need to know when is the last time they connected to my server?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:50:03

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Check out LoginTime...

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 18:26:39

SQL Profiler/SQL Trace is the best way to do it as the other methods will miss data. If you don't want to run the SQL Profiler GUI to do it as it causes peformance issues, then you can do it via SQL Trace which is the command version of SQL Profiler. Check out SQL Trace in SQL Server Books Online.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-28 : 19:54:07

Tara,
which trace will be good? I have never used before.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-28 : 22:55:02

Have you used SQL Profiler before?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-28 : 23:18:30

Yes i have used but not in T-sql command

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-29 : 00:58:15

SQL Profiler uses the same events as SQL Trace since SQL Trace is what is used in the background. Whatever you would have captured in the GUI for what you want to achieve here is what you would capture with SQL Trace.

Here are the stored procedures that you will need to call: sp_trace_create, sp_trace_setevent, and sp_trace_setstatus. There are others too if you want to set filters and do other things, but those should get you started. It'll require a lot of coding on your part, but it'll be worth it. I wrote a complex SQL Trace program recently and it took me about 80 work hours to complete. It's a solid product now though. I'm unable to share the product, but I can help if you have questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources