SQL Profiler and TEMP directory

In my last article I wrote about moving your temp directory to avoid problems if the system disc became full. If this does happen it could be a DBA running profiler that caused it!

Accepted best practices for running SQL Profiler application, says you should run it on a different machine to the server. This is good advice because it is not using processing or memory on the server, but also good because it will not be using disc space, specifically space on the temp directory.

Under “Space Requirements for SQL Server Profiler”, Books Online does say that you should move the temp directory and that SQL Profiler requires at lease 10 MB to function. The issue here is that it is a minimum value and system partitions don’t tend to be that large! To show this I started a standard trace, but added TSQL:StmtStarting  and SQL:StmtCompleted events. This created a file PrfF8F2.tmp in the C:\Users\Martin\AppData\Local\Temp directory. At a command prompt I then ran:

sqlcmd -E -S (local) -Q "WHILE 1=1 SELECT * FROM master.sys.databases"

This caused the file to grow by about 1MB per minute so after half-an-hour it was approaching 30MB. This file grow is nothing compared to a heavily used system.



I copied the file, and then stopped the trace. This didn’t destroy the original temporary file, so I closed down the trace window which did.

So what does this file contain?
I renamed the file PrfF8F2.trc and opened it with SQL Profiler. This showed that it contained all 161304 rows of trace events. Not only that but refreshing the TEMP directory showed that SQL Profiler had created another temporary file Prf3A1C.tmp, which was the same size as the trace I loaded!!!



What happens when you save the trace to a file?
SQL Profiler allows you to save profile data to a file as well as displaying on screen.



Running this trace did not stop the file being created in the temp directory, so therefore I had increased by two the disc writes and space used.

So as you can imagine running the SQL Profiler GUI can be very costly in terms of disc space, but hopefully you have set your TEMP directory to be somewhere other than the system disc.