SQL Profiler

Question

Post reply

SQL Profiler

b_boy

SSCertifiable

Points: 5145
More actions
July 24, 2008 at 8:57 am

#122677

Hello
I am trying to run sql profiler on a remote server, but I keep getting error messages relating login.
Can anyone help me out?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

b_boy SSCertifiable Points: 5145 More actions · Answer 1

Sorted!!!

Figured it to be the lead DBA not giving me access rights as an administrator (dbo) on the server itself

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

The Senior DBA only needed to grant ALTER TRACE rights.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 3

Can we schedule profiler at some specific time, becuase in profiler GUI i could find RUN button and the stop time option not start time option any where?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

In order to schedule a trace you would need to setup a server-side trace and put it in a SQL Server Agent job. You can setup the trace in Profiler, start it and stop it immediately. Then you can save the script that creates the trace by going to File -> Export -> Script Trace Definition and select either 2000 or 2005. Then you can put it in job.

BOL explains it fairly well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 5

Thanks Jack,

I have tried the script that creates the trace by going to File -> Export -> Script Trace Definition and select either 2000 or 2005.

and ran it throgh query analyser it failed with

Msg 19068, Level 16, State 1, Procedure sp_trace_create, Line 1

The trace file path is not valid or not supported.

(1 row(s) affected)

error.

When i put it into agent job the try to run the job it failed with :

Message

The job failed. Unable to determine if the owner (domani\user) of job for profiler has server access (reason: Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

One more question , is there any way to stop it after running from sql job?

Thanks in advance

DKG

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

IN the script you can change the location of the file to a valid location on the server. To stop a trace you use sp_trace_setstatus @trace_id = n, @status = 0, you can also edit the script to set the stop time. YOu can get the trace id from sys.traces.

Your error sounds like the AD server cannot be contacted. Also the user the job is running under needs ALTER TRACE Permissions.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 7

This time im running it from quary analyser with sa and specified the path C:\temptrace.trc

"temptrace" is the trace file which i ve created manually from profiler.

in first execution is give trace id 6 - as a result

and in second execution it failed with below error:

Msg 19067, Level 16, State 1, Procedure sp_trace_create, Line 1

Cannot create a new trace because the trace file path is found in the existing traces.

(1 row(s) affected)

i am a bit new in it and needs to get it done as per busness requirement.