SQL Server Profiler and the C drive space issue

As planned on Thursday, DBA Team was all set with the Change Control to run the SQL Server Profiler trace on the instance. The DBA started the trace using SQL Server Profiler and the trace files were getting stored on the D drive. Within a few minutes the trace for stopped stating there is no space on C drive. Since the C drive is “owned” by the System Admins team, the DBAs asked “Please clean it up”. Our System Admin team is very quick and they got back 6 GB of space within  no time. We know, what they do. Just delete the profiles of the DBA team from the server!

DBA all geared up for the second innings, started the Profiler trace again. Five minutes later, same issue. C drive out of space. This time it was obvious that the Profiler was creating the havoc. Hence the DBA got into action. There was a file Prf*.tmp file on C:\Documents and Settings\[DBAName]\Local Settings\Temp\2 directory which was 6 GB in size. This tmp file is used by the SQL Server Profiler to buffer the actual output trace files. Once the SQL Server Profiler is closed, these files are deleted automatically.

At times, the situations drive ones decision. The DBA followed the Application Team’s request as it is and forgot the basics in this case. “Never run the SQL Server Profiler trace on a production box, use Server Side traces instead“. The DBA knew it better and started to create the Server Side script for this trace.

Since we had the template ready all we need to do was create a script out of it and run it on the instance. Opened up SQL Server Profiler and started a new trace and stopped it immediately. Then clicked on File –> Export –> Script Trace Definition –> For SQL Server 2005

The script file was saved to the drive. In the script file, all the settings of the Template were exported except for the Output location of the trace file.

All we had to do was to replace InsertFileNameHere with the actual output path.

The .trc file extension is automatically appended to the file when the trace runs. Executed the script and made a note of the TraceID, so that we can monitor the trace execution.

Now the trace was running on the Server Side and the output files were getting generated as desired.

Bob, we are on the same page now 😉

3 thoughts on “SQL Server Profiler and the C drive space issue

  1. Robert L Davis

    Yes, this is one of the things I demo when I do SQL Profiler\Trace sessions at SQL Satuday or other events. Profiler stages data in the user’s temp space on the C drive. You can easily fill up the C drive and crash the server. I learned this the hard way many years ago by crashing a production server at 3 in the morning.

    Server-side traces do not stage the data first.

  2. Pingback: Opening Trace in SQL Server Profiler Using Disk Space Drija

  3. Pingback: Opening Trace in SQL Server Profiler Using Disk Space - Just just easy answers

Comments are closed.