How to load server size trace with roll-over

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RAlan - 22 Jul 2008 22:39 GMT

I'm using a server side trace with roll-over to log the activities on a
SQL 2005 server.
This generated huge traces.

When I try to load the report files with ::fn_trace_gettable onto
another sql instance, I get a tempdb full.

Obvious solution would be to increase the tempdb.
I would do that on last resort.

It would be great if there was a way to load the trace files one by one.
This would enable me to process the trace files as they roll over.

Does such functionality exist in 2005/2008?
What would you suggest to sole these problems?

RAlan

Reply to this Message

TheSQLGuru - 22 Jul 2008 23:07 GMT

Can you post the exact query you are using? I have read through GB-sized
tracefiles with no problems.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> I'm using a server side trace with roll-over to log the activities on a
> SQL 2005 server.
[quoted text clipped - 13 lines]
>
> RAlan

Reply to this Message

RAlan - 23 Jul 2008 00:17 GMT

The query I use is quite simple:

select    *
into    TracedData_020708
from    ::fn_trace_gettable( @curr_tracefilename, DEFAULT ) Trc
;

> Can you post the exact query you are using? I have read through GB-sized
> tracefiles with no problems.

Reply to this Message

TheSQLGuru - 23 Jul 2008 22:30 GMT

A minor (maybe not so minor) point is do you really need ALL of the fields
presented back by fn_trace_gettable? When I do analysis I am usually
importing only about 8-10 fields or so.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> The query I use is quite simple:
>
[quoted text clipped - 5 lines]
>> Can you post the exact query you are using? I have read through GB-sized
>> tracefiles with no problems.

Reply to this Message

Linchi Shea - 22 Jul 2008 23:16 GMT

The second parameter of fn_trace_gettable() already does what you want,
doesn't?

Linchi

> I'm using a server side trace with roll-over to log the activities on a
> SQL 2005 server.
[quoted text clipped - 13 lines]
>
> RAlan

Reply to this Message

RAlan - 23 Jul 2008 00:19 GMT

Yes, it partially does it:
if I'm correct, it starts loading from file number 1 each time :-{

It would be really great if it could be only file number x.

> The second parameter of fn_trace_gettable() already does what you want,
> doesn't?
[quoted text clipped - 18 lines]
>>
>> RAlan

Reply to this Message

Andrew J. Kelly - 23 Jul 2008 01:49 GMT

If you specify the filename of the file you want to start with and use a 1
as the second parameter it will only read that one. If you specify 4 it will
start from that file and read the next 4. But if you specify the original
file name used when you started the trace it will always start at the fist
file. The naming convention is easy to follow on the rollovers so just keep
track of which # file you read last and specify that with the next one.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Yes, it partially does it:
> if I'm correct, it starts loading from file number 1 each time :-{
[quoted text clipped - 23 lines]
>>>
>>> RAlan

Reply to this Message

RAlan - 23 Jul 2008 20:51 GMT

Andrew,

wow!
I've looked for it in the books online but it was not clear to me that
this could work.

I've tested it and it works OK.

Thanks
RAlan

> If you specify the filename of the file you want to start with and use a
> 1 as the second parameter it will only read that one. If you specify 4
[quoted text clipped - 3 lines]
> rollovers so just keep track of which # file you read last and specify
> that with the next one.

Reply to this Message