Profiler Help. - SQL Server Forums

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)

Profiler Help.

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-04-30 : 04:40:07

Hi
I need to track the connections hitting to my server through application???

I m doing some work in Profiler but i m not sure from where to start it..I m using SQL SERVER 2005...I m accesing few pages on application and that i want to track that activity involving at server side

T.I.A

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-30 : 10:41:02

Using profiler will be bad practice for this scenerio. Some connections might be hitting your server twice in a week and you won't be able to track it down.

1)Use batch files to track connection hitting your server
2)Schedule with windows task scheduler for 1 weeks and you will get reports regarding connections and apps.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-30 : 10:48:31

> 1)Use batch files to track connection hitting your server
what do you mean by that sodeep?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-30 : 11:26:57

Here is a contents of the batch file

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

:: @ECHO OFF

:: *********** Version 1.0 **************

SET APP=C:\WINDOWS\system32\netstat.exe

:TOP

SET FILEDATE=%DATE:~-10%

SET FILEDATE=%FILEDATE:/=_%

SET LOGFILE=D:\temp\netstat_out_%FILEDATE%.log

ECHO . >>%LOGFILE%

ECHO %DATE% %TIME% >>%LOGFILE%

%APP% -a >> %LOGFILE%

ECHO %DATE% %TIME% >>%LOGFILE%

ECHO . >>%LOGFILE%

exit

Set windows scheduler to schedule the task.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-30 : 11:46:14

aaaahhh i see. nice.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-02 : 02:22:45

Hi
I worked in profiler with my application got trace file :)

What I m seeing is that

SQL:BatchStarting select LockedIndicator from CIONLYLock
SQL:BatchCompleted select LockedIndicator from CIONLYLock
RPC:Completed
SQL:BatchStarting SELECT * Facility WHERE FID = '15'
SQL:BatchCompleted SELECT * Facility WHERE FID = '15'
SQL:BatchStarting SET NO_BROWSETABLE OFF
SQL:BatchCompleted SET NO_BROWSETABLE OFF
RPC:Completed
SQL:BatchStarting select LockedInd from Lock
SQL:BatchCompleted select LockedInd from Lock
Audit Logout
SQL:BatchStarting select LockedInd from Lock
SQL:BatchCompleted select LockedInd from Lock
SQL:BatchStarting casp_GetConnectionString 'sdfsg'
SQL:BatchCompleted casp_GetConnectionString 'sdfsg'
Audit Logout
SQL:BatchStarting select LockedInd from Lock
SQL:BatchCompleted select LockedInd from Lock

I need to know for each SQL:BatchStarting & SQL:BatchCompleted,RPC:Completed,Audit Logout
It is acquiring round trip???

Audit Logout means???? and after that

SQL:BatchStarting select LockedInd from Lock
SQL:BatchCompleted select LockedInd from Lock
SQL:BatchStarting casp_GetConnectionString 'DBLock'
SQL:BatchCompleted casp_GetConnectionString 'DBLock'

I m getting..i dint get Audit Login again :(

When i highlight

SQL:BatchStarting casp_GetConnectionString 'sdfsg'
SQL:BatchCompleted casp_GetConnectionString 'sdfsg'
Audit Logout
SQL:BatchStarting select LockedInd from Lock
SQL:BatchCompleted select LockedInd from Lock

I am getting below command in lower window pan

casp_GetConnectionString 'sdfsg'
go
select LockedInd from Lock
go

Please let me know this scenario....

2) what the Significance of SPID..in my application I am the only user hitting server but I am getting different SPID 55,56,57 & 58

T.I.A

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-02 : 14:31:59

Did you try the batch file i have given?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-02 : 14:34:27

under2811,

Are you using connection pooling?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-07 : 05:53:59

quote:
Originally posted by sodeep

Did you try the batch file i have given?

How to use this???

T.I.A

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-07 : 10:41:53

1)Create a folder called Temp in C:
2) save the batch file(.bat) I have given in C:\Temp
3) Using Window Scheduler ,schedule it to run every 5-10 minutes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources