Trace File

Question

Trace File

ramana3327

SSCoach

Points: 19700
More actions
October 6, 2014 at 6:43 am

#291299

I captured the trace on Machine A of SQL instance1. and keep the trace in D drive. I want to send that trace file to sql server table in instance2 in another machine B.
How to send that?

Viewing 15 posts - 1 through 15 (of 21 total)

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

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

Copy the file to machine B, then use the SQL function fn_trace_gettable

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

If the second server doesn't have access to the D drive of the first server you first need to copy the physical file to an accessible location. Next you can execute the following T-SQL on the second server:

SELECT

IDENTITY(int, 1, 1) AS RowNumber

, *

INTO [trace_table_name]

FROM ::fn_trace_gettable('D:\your_tracefile_name.trc', default)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

HanShi (10/6/2014)
If the second server doesn't have access to the D drive of the first server you first need to copy the physical file to an accessible location. Next you can execute the following T-SQL on the second server:
SELECT
IDENTITY(int, 1, 1) AS RowNumber
, *
INTO [trace_table_name]
FROM ::fn_trace_gettable('D:\your_tracefile_name.trc', default)

The :: hasn't been required since SQL 2000.

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

GilaMonster (10/6/2014)
HanShi (10/6/2014)
If the second server doesn't have access to the D drive of the first server you first need to copy the physical file to an accessible location. Next you can execute the following T-SQL on the second server:
SELECT
IDENTITY(int, 1, 1) AS RowNumber
, *
INTO [trace_table_name]
FROM ::fn_trace_gettable('D:\your_tracefile_name.trc', default)
The :: hasn't been required since SQL 2000.

Thanks for pointing that out. Thats the problem with scripts you use for a very long time. You don't take the time to update them as much as you need to.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ramana3327 SSCoach Points: 19700 More actions · Answer 5

If we have same trace in different files like Perftrace1, Perftrace2, Perftrace3.

How can I roll over all those in to single sql table?

HanShi SSC-Dedicated Points: 33506 More actions · Answer 6

Default it will read all sequential files, but you can add the desired number of files as an extra parameter,. See sys.fn_trace_gettable (Transact-SQL) for a complete explanation of this function.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

Or, if they're different traces, rather than rollover files, by using INSERT INTO instead of SELECT ... INTO

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

ramana3327 SSCoach Points: 19700 More actions · Answer 8

Little confuse about the profiler timings.

Correct me if I am wrong

When we selecting trace from Tools the time filter is in milliseconds but when we are monitoring from the trace also it will display millisec but when we sent to that into the sql server table, it will display in microsec.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 9

ramana3327 (10/6/2014)
Little confuse about the profiler timings.
Correct me if I am wrong
When we selecting trace from Tools the time filter is in milliseconds but when we are monitoring from the trace also it will display millisec but when we sent to that into the sql server table, it will display in microsec.

Correct!

The trace stores (file or table) the values in microseconds and displays (GUI) in miliseconds.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ramana3327 SSCoach Points: 19700 More actions · Answer 10

Thank you.

I am able to run the profiler from the tools under ssms and saving the ssrs trace in D-Drive. When I scriptout the trace template to run like server side trace and gave the same location to save the trace file, it is not working

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

What do you mean 'it's not working'?

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

ramana3327 SSCoach Points: 19700 More actions · Answer 12

ramana3327

SSCoach

Points: 19700

October 7, 2014 at 7:36 am

#1750581

It is giving that the path doesn't exist msg.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 13

ramana3327 (10/7/2014)
It is giving that the path doesn't exist msg.

Have you (double) checked the given path exists locally on the SQL Server? And does the SQL Server service account has modify permissions on that folder?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

ramana3327 (10/7/2014)
It is giving that the path doesn't exist msg.

You've double-checked that there are no spelling mistakes in the script, the server you're running the trace against has a local D drive and the path you've specified does exist?

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