May 29, 2002 at 6:56 am
Hello all,
I am capturing data using sql profiler. The trace data is being captured to my e:\ drive(NOT C\drive). I have set it to capture in 5mb increments. Before starting the trace, the size of C:\ drive was 2.3gb. I ran the trace for an hour and the size of e:\drive had increased by 400mb. There were many trace files in 5mb increments on e:\drive, this is as expected based on the trace data that I am capturing.
However, the size of c:\drive also increased by 400mb to 2.7gb. It was only when I closed sql profiler, the size of c:\drive shrunk back down to 2.3gb.
I have done this a number of times and the consequences are consistent. Even though I have stated to save trace file to a drive other than C:\drive, profiler seems to hold some sort temp file on c:drive.
This was never an issue on sql profiler on sql 7.
Has anyone come across this. Is there some configuraton that I can do. As it stands, I am unable to run large traces, because of the fear of filling the c:\drive.
thanks
Pargat
May 29, 2002 at 7:28 am
Were you able to see where the space was being used? Tempdb maybe? Or the temp folder on C?
Andy
May 29, 2002 at 8:21 am
It was not in tempdb as that was moved to a separate disk. Not sure which file on c:. will try looking if program files folder increases, which would indicating it is a sql server file.
May 29, 2002 at 8:39 am
Also look at the paging file, could be swapping or in the temp folder of the machine. Seems odd though the size would be relatively the same.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 3:34 am
Ok, I am getting somewhere. I am creating a trace on the server, logged on as local administrator.
I have worked out that on
c:\Document and Settings\Administrator\Tempa file called prfxxx.tmp (where xxx is a number), is generated.
The interesting thing is even though I have saved the trace file to a different drive in 5mb increments(which it successfully does), this tmp file just continues to grow.
For example, if the trace has written out 10 trace files 5mb in size to e:drive (totalling 50mb), the tmp file on C:\ would be 50mb.
I can then stop the trace but the tmp file will still be 50mb. It is only when a close down sql profiler, the actual trace file dissappears, and the space is reclaimed.
I now know what is happening, but Its seems wrong, any ideas ?
Pargat
May 30, 2002 at 5:26 am
I get the same after testing, 7 doesn't do it but 2000 Profiler does. Looks kind of like a failed auto recovery implementation like is used in Word. Will need to check with MS guys to find out why they did this for sure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 30, 2002 at 5:29 am
As a fix you can change the location of your temp folder.
Andy
May 30, 2002 at 6:43 am
I'll have a look on microsoft site, to see if it is a known problem
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply