SQL2k Profiler: load big trace file into table, on small system ?

17 Oct 2007 7:08 AM

Steve M

I am in a SQL2k environment, and am working on a machine with maybe 500 MB of
free space on its C:\ drive, where the SQL2k binaries are installed.

I have some profiler files I generated on another box, some up to 2.5 GB in
size.

When trying to open the files, I get the error: "System is low on disk space
on drive
'C'. All SQL Profiler functions are temporarily disabeled." Apparently
Profiler tries
to load the trace file, and in the process creates another temporary work
file, on C:, into which it tries to load the trace log. Apparently when
there's no more space on C:\, profiler stops, and then loads - just the
incomplete trace data read so far - from profiler into a SQL table I
specify..

I need to load the whole trace log file into the table. How can I do that in
the
existing environment ? Is there perhaps a reg key I can tweak to get the
temp file
re-directed to a different location, with more space ?

Thanks -

Steve

17 Oct 2007 10:04 AM

Martijn Tonies

Show quote

> I am in a SQL2k environment, and am working on a machine with maybe 500 MB
of
> free space on its C:\ drive, where the SQL2k binaries are installed.
>
> I have some profiler files I generated on another box, some up to 2.5 GB
in
> size.
>
> When trying to open the files, I get the error: "System is low on disk
space
> on drive
> 'C'. All SQL Profiler functions are temporarily disabeled." Apparently
> Profiler tries
> to load the trace file, and in the process creates another temporary work
> file, on C:, into which it tries to load the trace log. Apparently when
> there's no more space on C:\, profiler stops, and then loads - just the
> incomplete trace data read so far - from profiler into a SQL table I
> specify..
>
> I need to load the whole trace log file into the table. How can I do that
in
> the
> existing environment ? Is there perhaps a reg key I can tweak to get the
> temp file
> re-directed to a different location, with more space ?

A magical key that allows you to tweak the C drive size as in:

Read 500 Mb as to be 50 Gb so that my 2.5 Gb file will fit?

--
Martijn Tonies

17 Oct 2007 2:29 PM

Andrew J. Kelly

Why use profiler to load the trace into a table? Just dump it direct with
fn_trace_gettable()

select * INTO YourTable
FROM ::fn_trace_gettable('D:\YourTraceFile.trc', default)

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quote

"Steve M" <Ste***@discussions.microsoft.com> wrote in message
news:7F7D34B5-E738-4D04-AD9E-6EF0373CED9D@microsoft.com...
>I am in a SQL2k environment, and am working on a machine with maybe 500 MB
>of
> free space on its C:\ drive, where the SQL2k binaries are installed.
>
> I have some profiler files I generated on another box, some up to 2.5 GB
> in
> size.
>
> When trying to open the files, I get the error: "System is low on disk
> space
> on drive
> 'C'. All SQL Profiler functions are temporarily disabeled." Apparently
> Profiler tries
> to load the trace file, and in the process creates another temporary work
> file, on C:, into which it tries to load the trace log. Apparently when
> there's no more space on C:\, profiler stops, and then loads - just the
> incomplete trace data read so far - from profiler into a SQL table I
> specify..
>
> I need to load the whole trace log file into the table. How can I do that
> in
> the
> existing environment ? Is there perhaps a reg key I can tweak to get the
> temp file
> re-directed to a different location, with more space ?
>
> Thanks -
>
> Steve
>
>

17 Oct 2007 3:40 PM

Steve M

Wow. Thanks, I didn't know this. I never knew what the file format was.

Seems to work great ~

Thanks again,

Steve

Show quote

"Andrew J. Kelly" wrote:

> Why use profiler to load the trace into a table? Just dump it direct with
> fn_trace_gettable()
>
> select * INTO YourTable
> FROM ::fn_trace_gettable('D:\YourTraceFile.trc', default)
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Steve M" <Ste***@discussions.microsoft.com> wrote in message
> news:7F7D34B5-E738-4D04-AD9E-6EF0373CED9D@microsoft.com...
> >I am in a SQL2k environment, and am working on a machine with maybe 500 MB
> >of
> > free space on its C:\ drive, where the SQL2k binaries are installed.
> >
> > I have some profiler files I generated on another box, some up to 2.5 GB
> > in
> > size.
> >
> > When trying to open the files, I get the error: "System is low on disk
> > space
> > on drive
> > 'C'. All SQL Profiler functions are temporarily disabeled." Apparently
> > Profiler tries
> > to load the trace file, and in the process creates another temporary work
> > file, on C:, into which it tries to load the trace log. Apparently when
> > there's no more space on C:\, profiler stops, and then loads - just the
> > incomplete trace data read so far - from profiler into a SQL table I
> > specify..
> >
> > I need to load the whole trace log file into the table. How can I do that
> > in
> > the
> > existing environment ? Is there perhaps a reg key I can tweak to get the
> > temp file
> > re-directed to a different location, with more space ?
> >
> > Thanks -
> >
> > Steve
> >
> >
>
>

17 Oct 2007 2:30 PM

Andrew J. Kelly

Actually have a look at this tool as well:

http://www.cleardata.biz/cleartrace/

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quote

"Steve M" <Ste***@discussions.microsoft.com> wrote in message
news:7F7D34B5-E738-4D04-AD9E-6EF0373CED9D@microsoft.com...
>I am in a SQL2k environment, and am working on a machine with maybe 500 MB
>of
> free space on its C:\ drive, where the SQL2k binaries are installed.
>
> I have some profiler files I generated on another box, some up to 2.5 GB
> in
> size.
>
> When trying to open the files, I get the error: "System is low on disk
> space
> on drive
> 'C'. All SQL Profiler functions are temporarily disabeled." Apparently
> Profiler tries
> to load the trace file, and in the process creates another temporary work
> file, on C:, into which it tries to load the trace log. Apparently when
> there's no more space on C:\, profiler stops, and then loads - just the
> incomplete trace data read so far - from profiler into a SQL table I
> specify..
>
> I need to load the whole trace log file into the table. How can I do that
> in
> the
> existing environment ? Is there perhaps a reg key I can tweak to get the
> temp file
> re-directed to a different location, with more space ?
>
> Thanks -
>
> Steve
>
>

17 Oct 2007 10:12 PM

Erland Sommarskog

Steve M (Ste***@discussions.microsoft.com) writes:

> I need to load the whole trace log file into the table. How can I do
> that in the existing environment ? Is there perhaps a reg key I can
> tweak to get the temp file re-directed to a different location, with
> more space ?

Control Panel->System->Advanced->Environment Variables. Change TEMP and TMP.
These two environment variables are used by many programs in Windows.
You could start looking at what directories they point to now. Chances are
that you could find a lot to clean up there.

Of course, Andrews advice of using fn_trace_gettable is the right soluition
in this case. Nevertheless, the knowledge about %TEMP% and %TMP% may be
useful in the future.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

22 Nov 2007 10:53 AM

euDBA

I have a similary problem - still on SQL2k, however I've used Sever trace and
now have a multiple files with different names (date format file names). How
do I collectively upload them into a table??

Show quote

"Erland Sommarskog" wrote:

> Steve M (Ste***@discussions.microsoft.com) writes:
> > I need to load the whole trace log file into the table. How can I do
> > that in the existing environment ? Is there perhaps a reg key I can
> > tweak to get the temp file re-directed to a different location, with
> > more space ?
>
> Control Panel->System->Advanced->Environment Variables. Change TEMP and TMP.
> These two environment variables are used by many programs in Windows.
> You could start looking at what directories they point to now. Chances are
> that you could find a lot to clean up there.
>
> Of course, Andrews advice of using fn_trace_gettable is the right soluition
> in this case. Nevertheless, the knowledge about %TEMP% and %TMP% may be
> useful in the future.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

22 Nov 2007 4:05 PM

Andrew J. Kelly

You have to process them one at a time then. This will load the first file
and create the table.

select * INTO YourTable
FROM ::fn_trace_gettable('D:\YourTraceFile.trc', default)

Then change that to an INSERT INTO instead of the SELECT INTO and you can
load each of the other files as well.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quote

"euDBA" <eu***@discussions.microsoft.com> wrote in message
news:D6B5568F-762F-4F6A-92E8-895E6BA197FB@microsoft.com...
>I have a similary problem - still on SQL2k, however I've used Sever trace
>and
> now have a multiple files with different names (date format file names).
> How
> do I collectively upload them into a table??
>
> "Erland Sommarskog" wrote:
>
>> Steve M (Ste***@discussions.microsoft.com) writes:
>> > I need to load the whole trace log file into the table. How can I do
>> > that in the existing environment ? Is there perhaps a reg key I can
>> > tweak to get the temp file re-directed to a different location, with
>> > more space ?
>>
>> Control Panel->System->Advanced->Environment Variables. Change TEMP and
>> TMP.
>> These two environment variables are used by many programs in Windows.
>> You could start looking at what directories they point to now. Chances
>> are
>> that you could find a lot to clean up there.
>>
>> Of course, Andrews advice of using fn_trace_gettable is the right
>> soluition
>> in this case. Nevertheless, the knowledge about %TEMP% and %TMP% may be
>> useful in the future.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>

22 Nov 2007 10:49 PM

Erland Sommarskog

euDBA (eu***@discussions.microsoft.com) writes:

> I have a similary problem - still on SQL2k, however I've used Sever
> trace and now have a multiple files with different names (date format
> file names). How do I collectively upload them into a table??

In addition to Andrew's reply, if you want to avoid this in the future,
you may be interested in the fourth parameter to sp_trace_create:
@maxfilesize.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

23 Nov 2007 10:59 AM

euDBA

Thanks to Andrew and Erland althoughI was hoping there was an easier way to
do this as I've been collecting data since July.

I have cheated a bit and renamed the files to a common name with the _1, _2,
_3 extention and this seem to have done the trick just fine.

Show quote

"Erland Sommarskog" wrote:

> euDBA (eu***@discussions.microsoft.com) writes:
> > I have a similary problem - still on SQL2k, however I've used Sever
> > trace and now have a multiple files with different names (date format
> > file names). How do I collectively upload them into a table??
>
> In addition to Andrew's reply, if you want to avoid this in the future,
> you may be interested in the fourth parameter to sp_trace_create:
> @maxfilesize.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>