Tuesday, November 24, 2009

What I learned today – temp file created when reading from multiple trace files

I’m going to do some updating on past posts before moving forward on my BI learning. Today I want to close the book on using Profiler.

First, there’s an excellent series of three webcasts on Profiler on the MSDEV website. MSDEV is a great resource for anyone looking for webcasts on Microsoft developer products. Check out the site even if you’re familiar with Profiler, chances are you’ll find something you didn’t know before in one of the other webcast series.

Second, Profiler will create a temporary file when you read from multiple trace files. Like a lot of things, I found this out by accident. I had created a trace for replay on one of our production servers. It wasn’t until the trace had been running for a while that I noticed that I had forgotten to change the maximum size of the trace files. I let the trace complete, knowing I could read them all later. My trace ended up creating 369 .trc files totaling about 1.89 GB.

So I began reading all of the .trc files, meaning to save the results into one .trc file. While the trace was running, I noticed that the amount of free space on the C drive of my test server was dropping. It eventually bottomed out when the trace finished reading the files. And when I investigated, I found a file in my Local Settings\Temp folder that was the same size as the total size of the 369 .trc files. The temp file was deleted automatically when I closed the Profiler session. Finally I opened the single large .trc file I saved when reading the multiple smaller .trc files. This time no temporary files at all were created.

I wasn’t expecting the temp file to be created. Luckily this happened on my test server where i had enough free space. But it’s something to consider anyway. You may easily run out of space on your C drive without knowing why. I don’t know what would happen if I read the same trace with fewer, larger .trc files, that’s something I’ll need to look at for sure.

No comments: