How to run a Trace File without Profiler??

Question

Post reply

How to run a Trace File without Profiler??

MothInTheMachine

Hall of Fame

Points: 3251
More actions
June 19, 2009 at 7:53 am

#132016

Hi,
So I'm reading this very well written article on running a server-side trace and I have my trace file set up and ready to go when I realize that I do not know how to run it. I have 2 questions:
1. IS the trace file I've edited saved with a trc or sql extension?
2. I tried to run this in Query Analyzer but it just said 'The command(s) completed successfully' and Query batch Completed in the status window. How do I execute this?
3. Do I execute it on my production database server or do I do so remotely? I see nothing in the script that specifies which db to run it on s I'm thinkig ithas to run on the production server somewhere...or perhaps that is specified in whatever executes it??
Could someone help me out with this?
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Viewing 12 posts - 1 through 11 (of 11 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

This article posted as a link on SSC provides good example on how to setup a server side trace and how to query it.

http://www.builderau.com.au/program/sqlserver/soa/Write-custom-trace-files-in-TSQL/0,339028455,339282437,00.htm

Pradeep Singh

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 2

Thanks for the reply ps but I'm still not getting it. I've attached my trace file. I have saved it with a .trc file extension. I have it open in Query Analyzer and I hit f5 to run it. it puts a 128kb file into the specified directory but doesn't coninue to run.

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

MothInTheMachine (6/19/2009)
1. IS the trace file I've edited saved with a trc or sql extension?

If it's the SQL commands that you're talking about, it's a sql script, hence .sql

2. I tried to run this in Query Analyzer but it just said 'The command(s) completed successfully' and Query batch Completed in the status window. How do I execute this?

You just have. The trace is now running. Once it's completed (at whatever time you set as the end time) you can find the trace file (which is whereever you said it should go) and do whatever you plan to do with it. (open in profiler, copy somewhere else, import with fn_trace_gettable)

3. Do I execute it on my production database server or do I do so remotely? I see nothing in the script that specifies which db to run it on s I'm thinkig ithas to run on the production server somewhere...or perhaps that is specified in whatever executes it??

It has to be run against the production database server. It doesn't matter in the slightest if you run it from a querying tool on the server or remotely, no more than it matters where you execute a normal SQL query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 4

traces continue to run in the background and keep on writing captured information in the trace log file. You wont see anything other than what u saw in the query window. You will need to analyse the details captured in the trace file created by the trace. You can even import the details in a table, delete unwanted rows and then continue with analysis.

traces continue to run unless u stop it

exec sp_trace_setstatus @traceid = , @status = 0

Pradeep Singh

Lowell SSC Guru Points: 323518 More actions · Answer 5

you wouldn't open a trace file directly with notepad.

here's what i do:

i create a view for any trace i create, so I can easily access it.

here's a code example:

--review all my traces

select * from sys.traces

--find the path to the actual trace file, copy and paste it to the below

CREATE VIEW myDMLTrace AS

SELECT * FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\MyDMLtrace.trc', default)

then any time i want to see the trace reuslts, i simply do a SELECT * FROM myDMLTrace

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 6

I moved it to the production server and ran it. It created the trc file in the direcotry as expected but it s 9kb. Will the trc file only be filled after the trace stops running?

Thanks

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

ps. One Orange Chip Points: 29422 More actions · Answer 7

MothInTheMachine (6/19/2009)
I moved it to the production server and ran it. It created the trc file in the direcotry as expected but it s 9kb. Will the trc file only be filled after the trace stops running?
Thanks

It'll continue to run and write to the trace file continuously. May be it isn't getting enough events to capture and hence the size is not growing fast.

Pradeep Singh

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 8

It is still at 0kb. I ran through the pages of the site a few times so it should show something for a size. Does it matter that I made the script pointing to out development machine? I see no mention of a server anem in the script itself so I'm thinking this doesn't matter.

Thanks for you help with this!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

MothInTheMachine (6/19/2009)
It is still at 0kb. I ran through the pages of the site a few times so it should show something for a size.

Sometimes the size doesn't update immediatly, same with the update date. Don't worry about it, you can't open the file until the trace has finished, which will be at whatever you specified as the end time for the trace.

Does it matter that I made the script pointing to out development machine? I see no mention of a server anem in the script itself so I'm thinking this doesn't matter.

If you ran the query against the dev server, then the trace is running against the dev server. There's no servername mentioned because it's implied by the connection. Whichever server you run the script on is the one that the trace runs on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 10

It's up to 19,968 kb now. Maybe it only write periodically or when it stops running. I had it set for 30 minutes.

Thanks!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 11

Yes..apparenlty it is working fine. I wasn't aware of the lag in updating the file. now I can read Part 2 of the article 🙂

Thanks everyone!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;